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 ```

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