Compute YTD Using Analytic Function
-- Query to compute YTD value or any period to date (quarter-to-date) etc.
-- using Oracle analytic function and other funcations are used to create
-- calendar data.

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 LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12),
q_all AS (SELECT   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  s.date_id, t.quarter, s.sales,
 SUM(s.sales) OVER (PARTITION BY t.quarter  ORDER BY s.date_id)  sales_qtd,
 SUM(s.sales) OVER 
  (PARTITION BY SUBSTR(s.date_id,1,4) ORDER BY s.date_id) sales_ytd
FROM   q_sale s, q_all t
WHERE t.month_no = s.month_id;

-- Query to QTD/YTD summary for the full year

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

  387