Help with a query - 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 2008 Forums

Transact-SQL (2008)

Help with a query

Author

Topic

Centrofuge
Starting Member

3 Posts

Posted - 2010-10-28 : 18:43:41

Hi,

This is hopefully something very simple...

I am trying to show some monthly figures for each year next to each other as follows:

Month 2009 2010 2011
------------------------
Jan 500 600 750
Feb 250 550 1250
Mar 4200 100 2140

My problem is that my results are based on 2 columns - date and amount.

I can get the figures for individual years:


Select DATENAME (MONTH,ArrivalDate) as 'Month',SUM(TotalAmount) as '2010' 
from V_BillingEntriesFull
Where DATENAME (YEAR,ArrivalDate) = '2010'
Group by DATENAME (MONTH,ArrivalDate),DATENAME (YEAR,ArrivalDate)

Which gets me:
Month 2010
-----------
Jan 500
Feb 250
Mar 4200

but how do I get the results for each year next to each other?

Hope that makes sense!

Many thanks

Andy

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-28 : 19:45:38

You need a CASE statement or a PIVOT statement

Select DATENAME (MONTH,ArrivalDate) as 'Month'
,SUM(CASE WHEN YEAR(ArrivalDate) = 2009 THEN TotalAmount ELSE 0 END) as '2009'
,SUM(CASE WHEN YEAR(ArrivalDate) = 2010 THEN TotalAmount ELSE 0 END) as '2010'
,etc.
from V_BillingEntriesFull

Group by DATENAME (MONTH,ArrivalDate)

Everyday I learn something that somebody else already knew

Centrofuge
Starting Member

3 Posts

Posted - 2010-10-29 : 04:20:14

Fantastic, many thanks!

Centrofuge
Starting Member

3 Posts

Posted - 2010-10-29 : 06:11:21

One more thing :)

Is there a way I can dynamically name the column alias? So that I dont need to hard code the years?

Such as:

Select DATENAME (MONTH,ArrivalDate) as 'MonthPeriod'
,SUM(CASE WHEN YEAR(ArrivalDate) = (YEAR( Getdate())-2) THEN TotalAmount ELSE 0 END) As (YEAR( Getdate())-2)
,SUM(CASE WHEN YEAR(ArrivalDate) = (YEAR( Getdate())-1) THEN TotalAmount ELSE 0 END) As (YEAR( Getdate())-1)
etc..

I have tried to work it out myself but cant figure it out.

Thanks again

Andy

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 07:16:56

declare @s varchar(4000)
select @t = '
Select DATENAME (MONTH,ArrivalDate) as 'MonthPeriod'
,SUM(CASE WHEN YEAR(ArrivalDate) = (YEAR( Getdate())-2) THEN TotalAmount ELSE 0 END) As YEAR_' + convert(varchar(4),YEAR(Getdate())-2),
etc..
'
exec (@t)

Might case issues with permissions though depending on how it is used.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources