Get Quarter Report - 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)

Get Quarter Report

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-26 : 12:57:55

Afternoon,

I have two queries that I would like to combine into one. One query I do manually every quarter and one I found by using the search.

The first one shows me just the months in the quarter:


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)
FROM dbo.tblHealth

The second one gives me the total for the quarter we are in:


SELECT SUM(DATEPART(q, testdate))
FROM dbo.tblHealth

Is there a way to combine the two so I can say show me all the months in quarter 4 without using the case statement? What she wants is just to show the three months for that quarter.

Any help you can provide would be great.

Thanks

Laura

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-04-26 : 13:58:40

You could create a lookup table that defines a quarter i.e.


Quarter   Month
-------   -----------
Q1        January
Q1        February
Q1        March
etc.

Then you would join to this new table and do your calculations. Or have I mis-understood the question?

~Travis

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-26 : 15:39:26

If you just want to show the months in quarter 4, you can't just do this?

SELECT
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(q, testdate) = 4

Or, to get better performance:

SELECT
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 >= '2005-10-01'
AND datepart < '2006-01-01'

If you want to pivot the data and have a separate month for each column, you need to use CASE statements.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 02:21:24

Also refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-27 : 09:07:35

Thanks for all the great feedback.

I will check them out.

Laura

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 09:15:12

Also refer Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail