none
Multiple Dates Query

    Question

  • 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
    09-Actual      xxx xxx xxx xxx
    10-Actual      xxx xxx xxx xxx
    10-Target      xxx xxx xxx xxx
    10-Projection xxx xxx xxx xxx

    Any help is greatly apprciated.

    Thursday, September 23, 2010 7:15 PM

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 

    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by RudyCat Friday, September 24, 2010 3:46 PM
    • Edited by Arbi Baghdanian Friday, September 24, 2010 4:05 PM
    Friday, September 24, 2010 3:30 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
    Friday, September 24, 2010 2:24 PM

All replies

  • which RDBMS are you using?
    Thursday, September 23, 2010 7:27 PM
  • Microsoft SQL 2008

    Thursday, September 23, 2010 7:31 PM
  • Hi,

    Could you please show us some sample data and your expected result? It seems that you want to generate a cross-tab report. If so, please refer to the following links:

    http://www.mssqltips.com/tip.asp?tip=1019

    http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 24, 2010 5:06 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
    Friday, September 24, 2010 2:24 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.
    Friday, September 24, 2010 2:29 PM
  • 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
    2009-Actual      xxx xxx xxx xxx
    2010-Actual      xxx xxx xxx xxx
    2010-Target      xxx xxx xxx xxx
    2010-Projection xxx xxx xxx xxx

    In 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!!

    Friday, September 24, 2010 3:24 PM
  • 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
    2009-Actual      xxx xxx xxx xxx
    2010-Actual      xxx xxx xxx xxx
    2010-Target      xxx xxx xxx xxx
    2010-Projection xxx xxx xxx xxx

    In 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.

    Friday, September 24, 2010 3:29 PM
  • 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 

    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by RudyCat Friday, September 24, 2010 3:46 PM
    • Edited by Arbi Baghdanian Friday, September 24, 2010 4:05 PM
    Friday, September 24, 2010 3:30 PM
  • Thanks Arbi - I'll work with this and see what I can come up with
    Friday, September 24, 2010 3:46 PM
  • I only modified the original query that was written by Fritx(LearingSQL). 
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Friday, September 24, 2010 3:47 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,(@CurrentYear-1901)*12 + 4,0) then 1 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 7,0) then 2 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 10,0) then 3 
    						else 4
    					end as QuarterNo )t	 
    Where YearMonth >= dateadd(Year,@CurrentYear-1901,0) and YearMonth < dateadd(Year,@CurrentYear-1900,0) 
    

    Friday, September 24, 2010 4:00 PM
  • I modify the query. 
    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Friday, September 24, 2010 4:06 PM
  • 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,(@CurrentYear-1901)*12 + 4,0) then 1 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 7,0) then 2 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 10,0) then 3 
    						else 4
    					end as QuarterNo )t	 
    	Where YearMonth >= dateadd(Year,@CurrentYear-1901,0) and YearMonth < dateadd(Year,@CurrentYear-1900,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
    
    

    Friday, September 24, 2010 4:17 PM
  • 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,(@CurrentYear-1901)*12 + 4,0) then 1 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 7,0) then 2 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 10,0) then 3 
    						else 4
    					end as QuarterNo )t	 
    	Where YearMonth >= dateadd(Year,@CurrentYear-1901,0) and YearMonth < dateadd(Year,@CurrentYear-1900,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,(@CurrentYear-1901)*12 + 4,0) then 1 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 7,0) then 2 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 10,0) then 3 
    						else 4
    					end as QuarterNo )t	 
    	Where YearMonth >= dateadd(Year,@CurrentYear-1901,0) and YearMonth <= dateadd(Year,@CurrentYear-1900,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.
    Friday, September 24, 2010 4:43 PM
  • Small Problems,,, I need to change 4,7,10 to 3,6,9 

    and In YearMonth <= dateadd(Year,@CurrentYear-1900,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,(@CurrentYear-1901)*12 + 3,0) then 1 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 6,0) then 2 
    						when YearMonth < dateadd(Month,(@CurrentYear-1901)*12 + 9,0) then 3 
    						else 4
    					end as QuarterNo )t	 
    	Where YearMonth >= dateadd(Year,@CurrentYear-1901,0) and YearMonth < dateadd(Year,@CurrentYear-1900,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
    

    Friday, September 24, 2010 4:54 PM