Modeling months by years
ltps Nov 5, 2012 7:43 PMHello, everyone - I am new to the SQL model clause and have succeeded with some basics but am struggling to get to the next stage of learning with a requirement to list sums by month (across) and years (down), as in a spreadsheet.
I have dummied up some data in the WITH clause below. The dummy date is limited to a single year and cost center (for real, I will have multiple years and multiple cost centers, but I think I would be able to handle that). The goal is to list years as rows and months as columns with amounts in the cells. The SQL below works, but it feels wrong, and worse like I don't even understand the rules construction. The DISTINCT especially feels like a cheat. Here is the desired result (for one year only, ignoring the one cost center, for now):
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
2012 100 300 600 1000 1500 2100 2800 3600 4500 5500 6600 7800
On one topic in particular I would really like to get some clarification: with my real data (replacing the dummy data in the WITH clause), I have use windowing to create a running sum by month, which is the result I seek in the spreadsheet presentation, going across rather than down. Is this the wrong approach altogether? Should the accumulating sum rather be handled in the modeling rules?
Go ahead, tell me how dumb my attempt is, I can take it and learn! Thank you for your help.
WITH t AS (
SELECT 2012 AS period_year, 1234 AS cost_center, 'JAN' AS period_month, 1 AS period_number, 100 AS month_amount, 100 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'FEB' AS period_month, 2 AS period_number, 200 AS month_amount, 300 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'MAR' AS period_month, 3 AS period_number, 300 AS month_amount, 600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'APR' AS period_month, 4 AS period_number, 400 AS month_amount, 1000 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'MAY' AS period_month, 5 AS period_number, 500 AS month_amount, 1500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'JUN' AS period_month, 6 AS period_number, 600 AS month_amount, 2100 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'JUL' AS period_month, 7 AS period_number, 700 AS month_amount, 2800 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'AUG' AS period_month, 8 AS period_number, 800 AS month_amount, 3600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'SEP' AS period_month, 9 AS period_number, 900 AS month_amount, 4500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'OCT' AS period_month, 10 AS period_number, 1000 AS month_amount, 5500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'NOV' AS period_month, 11 AS period_number, 1100 AS month_amount, 6600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'DEC' AS period_month, 12 AS period_number, 1200 AS month_amount, 7800 AS cumulative_amount FROM dual
ORDER BY period_number
)
SELECT DISTINCT period_year
, JAN
, FEB
, MAR
, APR
, MAY
, JUN
, JUL
, AUG
, SEP
, OCT
, NOV
, DEC
FROM t
MODEL
PARTITION BY (period_year)
DIMENSION BY (period_month)
MEASURES ( cumulative_amount
, 0 AS JAN
, 0 AS FEB
, 0 AS MAR
, 0 AS APR
, 0 AS MAY
, 0 AS JUN
, 0 AS JUL
, 0 AS AUG
, 0 AS SEP
, 0 AS OCT
, 0 AS NOV
, 0 AS DEC
)
RULES (
JAN[ANY] = SUM(cumulative_amount)['JAN']
,FEB[ANY] = SUM(cumulative_amount)['FEB']
,MAR[ANY] = SUM(cumulative_amount)['MAR']
,APR[ANY] = SUM(cumulative_amount)['APR']
,MAY[ANY] = SUM(cumulative_amount)['MAY']
,JUN[ANY] = SUM(cumulative_amount)['JUN']
,JUL[ANY] = SUM(cumulative_amount)['JUL']
,AUG[ANY] = SUM(cumulative_amount)['AUG']
,SEP[ANY] = SUM(cumulative_amount)['SEP']
,OCT[ANY] = SUM(cumulative_amount)['OCT']
,NOV[ANY] = SUM(cumulative_amount)['NOV']
,DEC[ANY] = SUM(cumulative_amount)['DEC']
)
ORDER BY period_year
I have dummied up some data in the WITH clause below. The dummy date is limited to a single year and cost center (for real, I will have multiple years and multiple cost centers, but I think I would be able to handle that). The goal is to list years as rows and months as columns with amounts in the cells. The SQL below works, but it feels wrong, and worse like I don't even understand the rules construction. The DISTINCT especially feels like a cheat. Here is the desired result (for one year only, ignoring the one cost center, for now):
YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
2012 100 300 600 1000 1500 2100 2800 3600 4500 5500 6600 7800
On one topic in particular I would really like to get some clarification: with my real data (replacing the dummy data in the WITH clause), I have use windowing to create a running sum by month, which is the result I seek in the spreadsheet presentation, going across rather than down. Is this the wrong approach altogether? Should the accumulating sum rather be handled in the modeling rules?
Go ahead, tell me how dumb my attempt is, I can take it and learn! Thank you for your help.
WITH t AS (
SELECT 2012 AS period_year, 1234 AS cost_center, 'JAN' AS period_month, 1 AS period_number, 100 AS month_amount, 100 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'FEB' AS period_month, 2 AS period_number, 200 AS month_amount, 300 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'MAR' AS period_month, 3 AS period_number, 300 AS month_amount, 600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'APR' AS period_month, 4 AS period_number, 400 AS month_amount, 1000 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'MAY' AS period_month, 5 AS period_number, 500 AS month_amount, 1500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'JUN' AS period_month, 6 AS period_number, 600 AS month_amount, 2100 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'JUL' AS period_month, 7 AS period_number, 700 AS month_amount, 2800 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'AUG' AS period_month, 8 AS period_number, 800 AS month_amount, 3600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'SEP' AS period_month, 9 AS period_number, 900 AS month_amount, 4500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'OCT' AS period_month, 10 AS period_number, 1000 AS month_amount, 5500 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'NOV' AS period_month, 11 AS period_number, 1100 AS month_amount, 6600 AS cumulative_amount FROM dual UNION
SELECT 2012 AS period_year, 1234 AS cost_center, 'DEC' AS period_month, 12 AS period_number, 1200 AS month_amount, 7800 AS cumulative_amount FROM dual
ORDER BY period_number
)
SELECT DISTINCT period_year
, JAN
, FEB
, MAR
, APR
, MAY
, JUN
, JUL
, AUG
, SEP
, OCT
, NOV
, DEC
FROM t
MODEL
PARTITION BY (period_year)
DIMENSION BY (period_month)
MEASURES ( cumulative_amount
, 0 AS JAN
, 0 AS FEB
, 0 AS MAR
, 0 AS APR
, 0 AS MAY
, 0 AS JUN
, 0 AS JUL
, 0 AS AUG
, 0 AS SEP
, 0 AS OCT
, 0 AS NOV
, 0 AS DEC
)
RULES (
JAN[ANY] = SUM(cumulative_amount)['JAN']
,FEB[ANY] = SUM(cumulative_amount)['FEB']
,MAR[ANY] = SUM(cumulative_amount)['MAR']
,APR[ANY] = SUM(cumulative_amount)['APR']
,MAY[ANY] = SUM(cumulative_amount)['MAY']
,JUN[ANY] = SUM(cumulative_amount)['JUN']
,JUL[ANY] = SUM(cumulative_amount)['JUL']
,AUG[ANY] = SUM(cumulative_amount)['AUG']
,SEP[ANY] = SUM(cumulative_amount)['SEP']
,OCT[ANY] = SUM(cumulative_amount)['OCT']
,NOV[ANY] = SUM(cumulative_amount)['NOV']
,DEC[ANY] = SUM(cumulative_amount)['DEC']
)
ORDER BY period_year