how to select data for past 12 months from current date?

[yehong]

Hi guys, I have a sample table like the following:
Unit, Year , SubUnit , Category , Jan_Cases Feb_Cases , March_Cases... Dec_Cases
I have data for multiple years. I need to select data based upon current month -12 months. For example if current month is March, then the resulting columns should show month range of March 2007 to March 2008. The sample columns should look like folowing:

Unit,SubUnit,Category,Mar_Cases,Apr_Cases,May_Cases....Mar_Cases

Really appreciate your help.

 

[AlexCuse]

What version of SQL Server?

There are numerous FAQ's regarding pivoting data. I'd recommend doing it in whatever your reporting client may be.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[yehong]

SQL Server 2005. thanks.

 

[AlexCuse]

Look up the PIVOT command in books online.

I've never used it.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AlexCuse]

I actually don't know that PIVOT will do what you want (I'm not sure how it handles the column names).

what you'll need is a query like this:

[COLOR=blue]declare[/color] @piv [COLOR=blue]table[/color] (dt [COLOR=#FF00FF]datetime[/color], [COLOR=blue]cost[/color] [COLOR=blue]smallmoney[/color])

[COLOR=blue]insert[/color] @piv
[COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color](), 15
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color](), 50
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color]() - 30, 100
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color]() - 60, 32


[COLOR=blue]select[/color] sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]month[/color](dt) = 1 [COLOR=blue]then[/color] [COLOR=blue]cost[/color] [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] JanCost
	, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]month[/color](dt) = 2 [COLOR=blue]then[/color] [COLOR=blue]cost[/color] [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] FebCost
	, sum([COLOR=blue]case[/color] [COLOR=blue]when[/color] [COLOR=#FF00FF]month[/color](dt) = 3 [COLOR=blue]then[/color] [COLOR=blue]cost[/color] [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]) [COLOR=blue]as[/color] MarCost
[COLOR=blue]from[/color] @piv

However, since you need it for a dynamic range, you'd need to use Dynamic SQL to build and execute the query. I know I've demonstrated that to you before.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[yehong]

Alex, thanks for sample. I am thinking about using pivot logic. I have searched all around but could'nt find any sample for my situation. I am going to create a new post for that.