Answered by:
Multiple Dates Query

I need help in creating a query to get info for year selected and previous year of the year selected. Needs to be by quarter(QTR) for both years. QTR1 will always be the month 03, QTR2 month 06, QTR3 month 09, & QTR4 month 12. No computations are required or averaging.
Fields/columns are YEARMONTH stored as mm/dd/yyyy with dd always being 01, ACTUAL_MONTH(float)for all QTRs based on current YEARMONTH selected and one year prior,TARGET_MONTH(float) for all QTRs based on YEARMONTH selected, and PROJECTION_MONTH(float) for all QTRs based YEARMONTH selected.Table
YEARMONTH (PK, Datetime)
ACTUAL_MONTH(Float)
TARGET_MONTH(Float)
PROJECTION_MONTH(Float)
Should look like this if any YearMonth in 2010 was selected
Q1 Q2 Q3 Q4
09Actual xxx xxx xxx xxx
10Actual xxx xxx xxx xxx
10Target xxx xxx xxx xxx
10Projection xxx xxx xxx xxx
Any help is greatly apprciated.
Question
Answers

Declare @CurrentYear SmallInt Set @CurrentYear = 2010 Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Actual', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Actual_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Actual_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Actual_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Actual_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1 Union Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Taget', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Target_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Target_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Target_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Target_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1 Union Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Projection', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Projection_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Projection_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Projection_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Projection_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1
 Marked as answer by RudyCat Friday, September 24, 2010 3:46 PM
 Edited by Arbi Baghdanian Friday, September 24, 2010 4:05 PM

For this problem, you can use several approach to get the result. Some of them is to unified the result based on the QTR type. So, maybe its the simple way to get the result.
First, try to get just actual_month QTR based on the YearMonth given. The query will looks like:
SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Actual', Q1 = case when month(YearMonth) = 3 then Actual_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Actual_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Actual_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Actual_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010
The query above would return all Actual type QTR. So, the next step, with same approach, try to get Target type QTR and Projection QTR. After you get it, just union all the query using union all keyword.
The final query:
SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Actual', Q1 = case when month(YearMonth) = 3 then Actual_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Actual_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Actual_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Actual_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010 UNION ALL SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Target', Q1 = case when month(YearMonth) = 3 then Target_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Target_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Target_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Target_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010 UNION ALL SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Projection', Q1 = case when month(YearMonth) = 3 then Projection_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Projection_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Projection_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Projection_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010
Hope, it can enlighten you :)
Regards, fritz
Note: I haven't test the query, but i'm pretty sure it will work :)
 Marked as answer by RudyCat Friday, September 24, 2010 3:49 PM
All replies



Hi,
Could you please show us some sample data and your expected result? It seems that you want to generate a crosstab report. If so, please refer to the following links:
http://www.mssqltips.com/tip.asp?tip=1019
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the AllInOne Code Framework! If you have any feedback, please tell us. Proposed as answer by Naomi NModerator Friday, September 24, 2010 5:07 AM

For this problem, you can use several approach to get the result. Some of them is to unified the result based on the QTR type. So, maybe its the simple way to get the result.
First, try to get just actual_month QTR based on the YearMonth given. The query will looks like:
SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Actual', Q1 = case when month(YearMonth) = 3 then Actual_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Actual_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Actual_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Actual_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010
The query above would return all Actual type QTR. So, the next step, with same approach, try to get Target type QTR and Projection QTR. After you get it, just union all the query using union all keyword.
The final query:
SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Actual', Q1 = case when month(YearMonth) = 3 then Actual_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Actual_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Actual_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Actual_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010 UNION ALL SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Target', Q1 = case when month(YearMonth) = 3 then Target_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Target_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Target_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Target_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010 UNION ALL SELECT QTRType = cast(year(YearMonth as varchar(10)) + 'Projection', Q1 = case when month(YearMonth) = 3 then Projection_Month else 0 end, Q2 = case when month(YearMonth) = 6 then Projection_Month else 0 end, Q3 = case when month(YearMonth) = 9 then Projection_Month else 0 end, Q4 = case when month(YearMonth) = 12 then Projection_Month else 0 end FROM Table1 WHERE year(YearMonth) <= 2010
Hope, it can enlighten you :)
Regards, fritz
Note: I haven't test the query, but i'm pretty sure it will work :)
 Marked as answer by RudyCat Friday, September 24, 2010 3:49 PM

Fritz,
In your query better do something like this:
Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Projection', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Projection_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Projection_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Projection_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Projection_Month Else 0 End) From Table1 Where Year(YearMonth) = 2010
Best Wishes, Arbi  Please vote if you find this posting was helpful or Mark it as answered. 
Thanks Fritz & Arbi. I kinda understand where you are going with this but I still have to account for previous year as well. Any way of getting it in this format?
Q1 Q2 Q3 Q4
2009Actual xxx xxx xxx xxx
2010Actual xxx xxx xxx xxx
2010Target xxx xxx xxx xxx
2010Projection xxx xxx xxx xxxIn using the CASE statement I get all the months in that year.
Actual_Month
,
Q1
= case when month(YearMonth) = 3 then Actual_Month else 0 end,
Q2
= case when month(YearMonth) = 6 then Actual_Month else 0 end,
Q3
= case when month(YearMonth) = 9 then Actual_Month else 0 end,
Q4
= case when month(YearMonth) = 12 then Actual_Month else 0 end
FROM
[SPARS].[dbo].[t_kpi_Data]
WHERE
YearMonth Between '01/01/2010' AND '12/01/2010' AND DataSource_ID = '1038'
Still not getting previous year for Actual_Month based on yearmonth provided by user
Then I'll need to Matrix this in SSRS to create a chart. The QTRType = Cast line didn't work. The data (xxx) could be dollars, percents, dollars, or numbers and could be either negative or positive.
Again I appreciate your help!! 
Thanks Fritz & Arbi. I kinda understand where you are going with this but I still have to account for previous year as well. Any way of getting it in this format?
Q1 Q2 Q3 Q4
2009Actual xxx xxx xxx xxx
2010Actual xxx xxx xxx xxx
2010Target xxx xxx xxx xxx
2010Projection xxx xxx xxx xxxIn using the CASE statement I get all the months in that year.
Actual_Month
,
Q1
= case when month ( YearMonth ) = 3 then Actual_Month else 0 end ,
Q2
= case when month ( YearMonth ) = 6 then Actual_Month else 0 end ,
Q3
= case when month ( YearMonth ) = 9 then Actual_Month else 0 end ,
Q4
= case when month ( YearMonth ) = 12 then Actual_Month else 0 end
FROM
[SPARS] . [dbo] . [t_kpi_Data]
WHERE
YearMonth Between '01/01/2010' AND '12/01/2010' AND DataSource_ID = '1038'
Still not getting previous year for Actual_Month based on yearmonth provided by user
Then I'll need to Matrix this in SSRS to create a chart. The QTRType = Cast line didn't work. The data (xxx) could be dollars, percents, dollars, or numbers and could be either negative or positive.
Again I appreciate your help!!
Hi,WHERE Clause restricts it from getting actual_month from 2009.
Put Year condition in CASE Statement,
and Look for all years.
CASE WHEN year(year_month) = 2010 and month(yearMonth) = 3 then actual_month else 0 end.,
I hope you got my point.
Remove where condition and put that in CASE Statement will give you your desired output.

Declare @CurrentYear SmallInt Set @CurrentYear = 2010 Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Actual', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Actual_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Actual_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Actual_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Actual_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1 Union Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Taget', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Target_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Target_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Target_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Target_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1 Union Select QtrType = (Case Year(YearMonth) As Varchar(4)) + '  Projection', Q1= Sum(Case When Month(YearMonth) >= 1 And Month(YearMonth) <= 3 Then Projection_Month Else 0 End), Q2= Sum(Case When Month(YearMonth) >= 4 And Month(YearMonth) <= 6 Then Projection_Month Else 0 End), Q3= Sum(Case When Month(YearMonth) >= 7 And Month(YearMonth) <= 9 Then Projection_Month Else 0 End), Q4= Sum(Case When Month(YearMonth) >= 10 And Month(YearMonth) <= 12 Then Projection_Month Else 0 End) From Table1 Where Year(YearMonth) Between @CurrentYear And @CurrentYear  1
 Marked as answer by RudyCat Friday, September 24, 2010 3:46 PM
 Edited by Arbi Baghdanian Friday, September 24, 2010 4:05 PM



I didn't understand one point... y The query consists of 4 union parts all are same... no difference between them??
and although That Date Conditions looks simple, they will not allow you optimizer to properlu utilize the indexes..
We can write the same query like below.
Select QtrType = Cast( Year(YearMonth) As Varchar(4)) + '  Projection', Q1= Sum(Case When QuarterNo = 1 then Projection_Month else 0 end ), Q1= Sum(Case When QuarterNo = 2 then Projection_Month else 0 end ), Q1= Sum(Case When QuarterNo = 3 then Projection_Month else 0 end ), Q1= Sum(Case When QuarterNo = 4 then Projection_Month else 0 end ) From Table1 cross apply (select case when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 4,0) then 1 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 7,0) then 2 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 10,0) then 3 else 4 end as QuarterNo )t Where YearMonth >= dateadd(Year,@CurrentYear1901,0) and YearMonth < dateadd(Year,@CurrentYear1900,0)


Now we can write this query like below... In that case we need to scan 3 times that table. Now in single scan, we can do like below..
declare @CurrentYear int set @CurrentYear = 2010 ;with cte as ( select [Actual_Month],[Target_Month],[Projection_Month],QuarterNo from Table1 cross apply (select case when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 4,0) then 1 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 7,0) then 2 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 10,0) then 3 else 4 end as QuarterNo )t Where YearMonth >= dateadd(Year,@CurrentYear1901,0) and YearMonth < dateadd(Year,@CurrentYear1900,0) ), cte1 as ( select QuarterNo,Category,SUM(val) as Maxval from ( select * from cte c unpivot (val for Category in ([Actual_Month],[Target_Month],[Projection_Month]))pvt )p ) select Category,[1] as Q1,[2] as Q2,[3] as Q3,[4] as Q4 from cte1 pivot (sum(Maxval) for QuarterNo in ([1],[2],[3],[4]))pvt

Now we can write this query like below... In that case we need to scan 3 times that table. Now in single scan, we can do like below..
declare @CurrentYear int set @CurrentYear = 2010 ;with cte as ( select [Actual_Month],[Target_Month],[Projection_Month],QuarterNo from Table1 cross apply (select case when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 4,0) then 1 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 7,0) then 2 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 10,0) then 3 else 4 end as QuarterNo )t Where YearMonth >= dateadd(Year,@CurrentYear1901,0) and YearMonth < dateadd(Year,@CurrentYear1900,0) ), cte1 as ( select QuarterNo,Category,SUM(val) as Maxval from ( select * from cte c unpivot (val for Category in ([Actual_Month],[Target_Month],[Projection_Month]))pvt )p ) select Category,[1] as Q1,[2] as Q2,[3] as Q3,[4] as Q4 from cte1 pivot (sum(Maxval) for QuarterNo in ([1],[2],[3],[4]))pvt
Ramireddy,
I try your query but unfortunately it is not working fine. Also modified to show the data based on year also.
I like your idea of using PIVOT/UNPIVOT as well.
declare @CurrentYear int set @CurrentYear = 2010 Declare @myTable Table (YearMonth DateTime, Actual_Month Float, Target_Month Float, Projection_Month Float ) Insert Into @myTable Select '06/01/09', 1, 5, 50 Union All Select '07/01/09', 2, 6, 60 Union All Select '08/01/09', 3, 7, 70 Union All Select '09/01/09', 4, 8, 80 Union All Select '10/01/09', 5, 9, 90 Union All Select '11/01/09', 6, 10, 100 Union All Select '12/01/09', 7, 11, 110 Union All Select '01/01/10', 8, 12, 120 Union All Select '02/01/10', 9, 13, 130 Union All Select '03/01/10', 10, 14, 140 Union All Select '04/01/10', 11, 15, 150 Union All Select '05/01/10', 12, 16, 160 Union All Select '06/01/10', 13, 17, 170 Union All Select '07/01/10', 14, 18, 180 Union All Select '08/01/10', 15, 19, 190 Union All Select '09/01/10', 16, 20, 200 Select * From @myTable ; with cte as ( select [Actual_Month],[Target_Month],[Projection_Month],QuarterNo, Year(YearMonth) As SelectedYear from @myTable cross apply (select case when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 4,0) then 1 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 7,0) then 2 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 10,0) then 3 else 4 end as QuarterNo )t Where YearMonth >= dateadd(Year,@CurrentYear1901,0) and YearMonth <= dateadd(Year,@CurrentYear1900,0) ), cte1 as ( select SelectedYear, QuarterNo,Category,SUM(val) as Maxval from ( select * from cte c unpivot (val for Category in ([Actual_Month],[Target_Month],[Projection_Month]))pvt )p Group By SelectedYear, QuarterNo, Category ) select SelectedYear, Category,[1] as Q1,[2] as Q2,[3] as Q3,[4] as Q4 from cte1 pivot (sum(Maxval) for QuarterNo in ([1],[2],[3],[4]))pvt
Best Wishes, Arbi  Please vote if you find this posting was helpful or Mark it as answered. 
Small Problems,,, I need to change 4,7,10 to 3,6,9
and In YearMonth <= dateadd(Year,@CurrentYear1900,0) , removing the "=" symbol...
I didn't test against the data..... If he gave the data, I could have tested it..
Now its working fine..check it..
declare @CurrentYear int set @CurrentYear = 2010 Declare @myTable Table (YearMonth DateTime, Actual_Month Float, Target_Month Float, Projection_Month Float ) Insert Into @myTable Select '06/01/09', 1, 5, 50 Union All Select '07/01/09', 2, 6, 60 Union All Select '08/01/09', 3, 7, 70 Union All Select '09/01/09', 4, 8, 80 Union All Select '10/01/09', 5, 9, 90 Union All Select '11/01/09', 6, 10, 100 Union All Select '12/01/09', 7, 11, 110 Union All Select '01/01/10', 8, 12, 120 Union All Select '02/01/10', 9, 13, 130 Union All Select '03/01/10', 10, 14, 140 Union All Select '04/01/10', 11, 15, 150 Union All Select '05/01/10', 12, 16, 160 Union All Select '06/01/10', 13, 17, 170 Union All Select '07/01/10', 14, 18, 180 Union All Select '08/01/10', 15, 19, 190 Union All Select '09/01/10', 16, 20, 200 Select * From @myTable ;with cte as ( select [Actual_Month],[Target_Month],[Projection_Month],QuarterNo, Year(YearMonth) As SelectedYear from @myTable cross apply (select case when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 3,0) then 1 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 6,0) then 2 when YearMonth < dateadd(Month,(@CurrentYear1901)*12 + 9,0) then 3 else 4 end as QuarterNo )t Where YearMonth >= dateadd(Year,@CurrentYear1901,0) and YearMonth < dateadd(Year,@CurrentYear1900,0) ), cte1 as ( select SelectedYear, QuarterNo,Category,SUM(val) as Maxval from ( select * from cte c unpivot (val for Category in ([Actual_Month],[Target_Month],[Projection_Month]))pvt )p Group By SelectedYear, QuarterNo, Category ) select SelectedYear, Category,[1] as Q1,[2] as Q2,[3] as Q3,[4] as Q4 from cte1 pivot (sum(Maxval) for QuarterNo in ([1],[2],[3],[4]))pvt