MSSQL Query on a Query??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

MSSQL Query on a Query??

Post 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 553 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MSSQL Query on a Query??

Post by VladSun »

Use HAVING after GROUP BY - it's the same as WHERE but it is used on rows produced by GROUP BY
There are 10 types of people in this world, those who understand binary and those who don't
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

Re: MSSQL Query on a Query??

Post 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'.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MSSQL Query on a Query??

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

Re: MSSQL Query on a Query??

Post by JimiH »

Found the answer here

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

The dreaded, evil "select star" apparently!

thanks

Geoff
Post Reply