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

Answer 1

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