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.

Answer 1

which RDBMS are you using?

Answer 2

Microsoft SQL 2008

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

Thanks Arbi - I'll work with this and see what I can come up with

Answer 10

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.

Answer 11

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) 

Answer 12

I modify the query. 

---

Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.

Answer 13

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

Answer 14

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.

Answer 15

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