MSSQL Query on a Query??
Posted: Wed Jul 16, 2008 7:34 am
Hello
I have a query which displays data in a specified way
This displays a table like the following
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
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
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