-- 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_all t LEFT OUTER JOIN q_sale s
ON    t.month_no = s.month_id
AND   t.date_id = s.date_id
;

-- Query to QTD/YTD summary for Available Data