Show Select Months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Show Select Months

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-12-06 : 12:59:22

Afternoon,

I'm sure this is easy but I can't seem to get the syntax right or maybe I'm going about it the wrong way. I need a summary total by month so I did a case statement to make a "pivot" table now I want the user to be able to select the months she wants to see. I still get all the months.
Below is the code if someone could point me in the right direction I would appreciate it.

Thanks,

Laura


SELECT 
January = SUM(CASE WHEN DATENAME(month, testdate) ='January' THEN 1 ELSE 0 END),
February = SUM(CASE WHEN DATENAME(month, testdate) ='February' THEN 1 ELSE 0 END),
March = SUM(CASE WHEN DATENAME(month, testdate) ='March' THEN 1 ELSE 0 END),
April = SUM(CASE WHEN DATENAME(month, testdate) ='April' THEN 1 ELSE 0 END),
May = SUM(CASE WHEN DATENAME(month, testdate) ='May' THEN 1 ELSE 0 END),
June = SUM(CASE WHEN DATENAME(month, testdate) ='June' THEN 1 ELSE 0 END),
July = SUM(CASE WHEN DATENAME(month, testdate) ='July' THEN 1 ELSE 0 END),
August = SUM(CASE WHEN DATENAME(month, testdate) ='August' THEN 1 ELSE 0 END),
September = SUM(CASE WHEN DATENAME(month, testdate) ='September' THEN 1 ELSE 0 END),
October = SUM(CASE WHEN DATENAME(month, testdate) ='October' THEN 1 ELSE 0 END),
November = SUM(CASE WHEN DATENAME(month, testdate) ='November' THEN 1 ELSE 0 END),
December = SUM(CASE WHEN DATENAME(month, testdate) ='December' THEN 1 ELSE 0 END)
FROM dbo.tblHealth
where DATEPART(month, testdate)between '10' and '12'

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-06 : 14:53:23

It would be easier/faster to use the MONTH function

Select January = sum(case when month(testdate) = 1 then WhateverFieldValueIWant-OR-1 else 0 end),
February = sum(case when month(testdate) = 2 then WhateverFieldValueIWant-OR-1 else 0 end)
etc

Questions:
1. Not sure why you were including all of the months but where then trying to just use October Thru December's data in the where clause.
2. Notice I put WhateverFieldValueIWant-OR-1 in the THEN clause, because I wasn't sure if you just wanted a count of the times that month was in the table or if you want to pull some kind of value like PeopleSick or something. If you want the SUM of a field just put the field name in, if you just wanted a count then just use the 1 as you initially had.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources