# 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

• ```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 Friday, September 24, 2010 3:46 PM
• Edited by 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 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 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.

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.

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 Friday, September 24, 2010 3:46 PM
• Edited by 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
```

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
),
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
),
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
),
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
),
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