| Data is created using DBMS_RANDOM Oracle package to model sales data for previous and current year (ref. Data Creation Script ). |
| The sales data analysis is typically performed on data warehouse objects (tables, views). The query table (q_fc in the query shown below) represents a time/period dimension table with defined periods (fiscal calendar etc.). |
| Create previous year's equivalent monthly value using Oracle analytic function LAG() OVER (PARTITION BY .. ORDER BY ..) |
| SELECT TO_NUMBER(q_fc.year_id) year_id, q_fc.month_id, fss.sales_amount revenue |
| 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 (+) |
| ORDER BY q_fc.year_id; |
72927