asking for SQL query

i am developing a account statement so when the user click on the link to view the February link it will show the transaction from Jan - Feb ,
how to format it to a query
i have the sample fo the following table:

ID trans_desc trans_date
-------------
1 testing 1/9/2003 11:33:00 AM
2 testing1 2/9/2003 11:33:00 AM
3 testing2 1/9/2004 11:33:00 AM

how to i extract the transaction description month from the month Jan-Feb . how to i do for the SQL queryIf there are 50 people on this forum, you'll get 50 different ways to do this. Here is one:

declare @.SelectedDate datetime
set @.SelectedDate = '2003-02-15'

select *
from YourTable
where trans_date >= convert(char(4), @.SelectedDate, 120)+'-01-01'
and trans_date < dateadd(month, 1, convert(char(7), @.SelectedDate, 120)+'-01')

Ascending and count help

Below is the statement i am working with and i am trying to figure out
how to have the count in ascending order...is this possible? Right now
it groups by FP in alphabetical order but i dont want that i want the
whatever the FP1 count is to be ascending but i am not sure how to go
about doing this...so any help or ideas?

SELECT FP1 AS FP, COUNT(FP1) AS Expr1
FROM dbo.FP1
WHERE (FP1 IS NOT NULL) AND (LTRIM(RTRIM(FP1)) <> '')
GROUP BY FP1ORDER BY is the way (the only way) to sort the result of a query.

Just add

ORDER BY expr1

--
David Portas
SQL Server MVP
--|||Thank you...that worked :)

David Portas wrote:
> ORDER BY is the way (the only way) to sort the result of a query.
> Just add
> ORDER BY expr1
> --
> David Portas
> SQL Server MVP
> --|||Thank you...that worked :)

David Portas wrote:
> ORDER BY is the way (the only way) to sort the result of a query.
> Just add
> ORDER BY expr1
> --
> David Portas
> SQL Server MVP
> --

ASC / DESC based on CASE

Hi is it possible to sort data ASC or DESC using a CASE Statement based on
user input parameter ?
ThanksYes, use separate CASE expressions in the ORDER BY clause.
Anith|||On Tue, 3 Jan 2006 15:06:51 -0800, Vishal wrote:

>Hi is it possible to sort data ASC or DESC using a CASE Statement based on
>user input parameter ?
>Thanks
>
Hi Vishal,
Yes - but not the exact way that you think/hope for.
Example #1:
SELECT yadda, yadda
FROM whatever
ORDER BY CASE WHEN @.Direction = 'ASC' THEN SortColumn END ASC,
CASE WHEN @.Direction = 'DESC' THEN SortColumn END DESC
Example #2 (works only for numeric columns)
SELECT yadda, yadda
FROM whatever
ORDER BY SortColumn * CASE WHEN @.Direction = 'ASC' THEN 1 ELSE -1 END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||note that this one-size-fits-all approach may perform poorly, as
described here
http://www.devx.com/dbzone/Article/30149/0/page/2

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS/400 DB2 and SQL Server..

Hi Guys,
We are using Linked server to access DB2 on AS/400 from SQL Server. Well, things are fine when we test with SELECT statement. When we use update etc... a strange error comes up. Cannot open File 'C:\Program Files\DataDirect\ODBC41\Drivers\.'. Well, on the first time I did an update it seemed like it worked. But I had given the wrong Criteria so nothing got updated. But going forward, I am always getting this error. Can somebody please help me.
Thanks in advance
AshokIt is ok now guys. It turned out we had not set up the journal properly on the AS/400. It works great now.

AS Configuration

Hi

I try to run the following statement in “Server A” and it works fine then I make a copy of my cube (with backup & restore) in “Server B” and the MDX statement does not work.

My statement:

With

member [Measures].[Acum_Amount] as

'sum(

( YTD([Time].[Time].CurrentMember)

, [Scenario].[Scenario].&[PLAN])

, [Measures].[ Amount]

)/1000'

Select [Measures].[Acum_Amount] on 0

, [Time]. [Time].[Year].&[2005E3].&[Q1/ 2005].&[2/ 2005]

From [FT_BAB]

If I remove the code in red, it works in both servers. But it is not what i want.

What is wrong with the server configuration?

The SUM() function takes a set as its first argument and you are passing a tuple.

Sum( Set_Expression [ , Numeric_Expression ] )

I think what you are looking for on both servers is the following:

With

member [Measures].[Acum_Amount] as

'sum(

YTD([Time].[Time].CurrentMember),

([Scenario].[Scenario].&[PLAN], [Measures].[ Amount])

)/1000'

|||

You are right. Your syntax works in both of the servers. I will replace it.

I was confused because the other syntax works in one of the servers.

Thanks!!!!!!!!!!!!!!