Page 1 of 1

MSSQL Query on a Query??

Posted: Wed Jul 16, 2008 7:34 am
by JimiH
Hello

I have a query which displays data in a specified way

Code: Select all

SELECT Count([ECR].ECR_Number) AS CountOfECR_Number,CAST(DATEPART(mm, CreatedDate) AS NVARCHAR)
+ '/'+ CAST(DATEPART(yy, CreatedDate) AS NVARCHAR)AS fn,CASE CAST(DATEPART(mm, CreatedDate) AS NVARCHAR)
+ '/'+ CAST(DATEPART(yy, CreatedDate) AS NVARCHAR)
 
WHEN '10/2007' THEN 1 
WHEN '11/2007' THEN 2 
WHEN '12/2007' THEN 3 
WHEN '1/2008' THEN 4
WHEN '2/2008' THEN 5 
WHEN '3/2008' THEN 6 
WHEN '4/2008' THEN 7 
WHEN '5/2008' THEN 8 
WHEN '6/2008' THEN 9 
WHEN '7/2008' THEN 10 
WHEN '8/2008' THEN 11
WHEN '9/2008' THEN 12
ELSE '0'
END as Finmth
 
from ECR
 
WHERE [ecr].LocationID = '7' 
 
Group by CAST(DATEPART(mm, CreatedDate) AS NVARCHAR)
+ '/'+ CAST(DATEPART(yy, CreatedDate) AS NVARCHAR)
 
Order by Finmth
 
This displays a table like the following
tab.jpg
tab.jpg (46.11 KiB) Viewed 555 times
How do I get the query to ignore the '0' values in the "Finmth" col so I'm just left with the values 1 thru 10

Thanks

Geoff

Re: MSSQL Query on a Query??

Posted: Wed Jul 16, 2008 7:39 am
by VladSun
Use HAVING after GROUP BY - it's the same as WHERE but it is used on rows produced by GROUP BY

Re: MSSQL Query on a Query??

Posted: Wed Jul 16, 2008 8:26 am
by JimiH
Thanks

I added

Code: Select all

 
 
HAVING Finmth ='0'
 
 
I now get this?

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Finmth'.

Re: MSSQL Query on a Query??

Posted: Wed Jul 16, 2008 8:52 am
by VladSun
Well, obviously column name alias (i.e. by using AS) can't be used in GROUP BY, HAVING, WHERE etc. clauses ...
I don't know much of T_SQL, but if it's really so ... stupid MS SQL...

So ... probably you'll need to copy-paste the whole expression for the column, like you do in GROUP BY

Re: MSSQL Query on a Query??

Posted: Wed Jul 16, 2008 9:33 am
by JimiH
Found the answer here

http://forums.devshed.com/ms-sql-develo ... 54974.html

The dreaded, evil "select star" apparently!

thanks

Geoff