Using the LAG() function, we create the monthly prior year value for the corresponding month of current year. The LAG() function returns 0 for 201201 and 34438 (which is previous year's January value) for 201301. The PARTITION BY month_id partitions the data by month value (1,1,2,2,3,3,.. etc) and ORDER BY year_id arranges them by year_id values (201201, 201301, 201202, 201302 etc.) for each row of the query as shown in output below. |
SELECT q_m24.year_id, q_m24.month_id, q_m24.sales cy_sales, |
LAG(q_m24.sales,1,0) |
OVER(PARTITION BY q_m24.month_id ORDER BY q_m24.year_id) py_sales |
FROM |
(SELECT TO_NUMBER(q_fc.year_id) year_id, q_fc.month_id, fss.sales_amount sales |
FROM |
fact_sales_summary fss, |
(SELECT TO_CHAR(SYSDATE,'yyyy')||LPAD(level,2,'0') year_id, level month_id |
FROM DUAL CONNECT BY LEVEL <= 12 |
UNION |
SELECT TO_CHAR(SYSDATE,'yyyy')-1||LPAD(level,2,'0') year_id, level month_id |
FROM DUAL CONNECT BY LEVEL <= 12 |
) q_fc |
WHERE q_fc.year_id = fss.month_id (+) |
) q_m24; |
By restricting the result of the above query to current year only (2013), we setup the data for analysis of current year versus previous year (2012). The monthly and annual variance and related percentages can be computed as shown in final query. |
74122