Use of Oracle Analytic Function LAG() OVER (PARTITION BY .. ORDER BY ..)
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.



Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: 10/27/2013

  74122