Compute YTD Using Analytic Function

-- Query to compute YTD value or any period to date (quarter-to-date) etc. for the full year.

WITH q_sales AS
(SELECT 201301 date_id, 1550 sales FROM DUAL UNION
 SELECT 201302 date_id, 1750 sales FROM DUAL UNION
 SELECT 201304 date_id, 1450 sales FROM DUAL UNION
 SELECT 201305 date_id, 1625 sales FROM DUAL UNION
 SELECT 201307 date_id, 1975 sales FROM DUAL UNION
 SELECT 201308 date_id, 1995 sales FROM DUAL UNION
 SELECT 201310 date_id, 1965 sales FROM DUAL),
q_qtr AS  (SELECT 2013||LPAD(LEVEL,2,'0') date_id, LEVEL v_val  FROM DUAL CONNECT BY LEVEL <=12),
q_all AS  (SELECT SUBSTR(q_qtr.date_id,1,4) year_id, q_qtr.date_id, q_qtr.v_val month_no,
            DECODE(GREATEST(q_qtr.v_val,4), q_qtr.v_val,
             DECODE(GREATEST(q_qtr.v_val,7), q_qtr.v_val,
              DECODE(GREATEST(q_qtr.v_val,10), q_qtr.v_val,4,3),2),1) Quarter  FROM q_qtr),
q_sale AS (SELECT  date_id, TO_NUMBER(SUBSTR(date_id,5)) month_id, sales   FROM q_sales)
SELECT  t.date_id, t.quarter, s.sales,
 SUM(s.sales) OVER (PARTITION BY t.quarter  ORDER BY t.date_id)  sales_qtd,
 SUM(s.sales) OVER (PARTITION BY t.year_id  ORDER BY t.date_id) sales_ytd
FROM   q_sale s, q_all t
WHERE t.month_no = s.month_id (+)
AND   t.date_id = s.date_id (+)
;

-- Query to QTD/YTD summary for Available Data

-- Query to QTD/YTD summary YTD in ANSI SQL
Compute YTD Using Analytic Function

Refer Other Analytic Function Queries
1. Cummulative Totals
2. Year Over Year Sales

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 18th, 2014

  428