SELECT q_m12.year_id, q_m12.cy_sales, q_m12.py_value py_sales,
|
q_m12.cy_sales - q_m12.py_value growth_amt,
|
ROUND(100*(q_m12.cy_sales/q_m12.py_value-1),3) monthly_pct,
|
SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id) cy_sales_ytd,
|
SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id) py_sales_ytd,
|
SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id) -
|
SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id) growth_ytd,
|
ROUND(100*(SUM(q_m12.cy_sales) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id)/
|
SUM(q_m12.py_value) OVER (PARTITION BY q_m12.c_year
|
ORDER BY q_m12.month_id)-1),3) ytd_pct
|
FROM
|
(SELECT SUBSTR(q_m24.year_id,1,4) c_year, 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_value
|
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
|
) q_m12
|
WHERE q_m12.c_year = TO_CHAR(SYSDATE,'YYYY');
|