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 (46.11 KiB) Viewed 554 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
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
There are 10 types of people in this world, those who understand binary and those who don't