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