-- In some institutions, active business days are Monday through Friday.  For reporting 
-- purposes, Saturday, Sunday data should use previous Friday's data.  Since this is a POC, 
-- TRUNC(SYSDATE,'MON') would be the recommended approach.  This is a alternative way to 
-- avoid storing same data in a table.

CREATE OR REPLACE VIEW v_all_201401_data 
AS
WITH qt_cal AS
 (SELECT
    level  mth_day
   ,qt_mth.start_date+level-1  cal_date
   ,TO_CHAR(qt_mth.start_date+level-1,'DY')  wk_day
   ,TO_CHAR(CASE
     WHEN TO_CHAR(qt_mth.start_date+level-1,'DY') IN ('SAT','SUN') 
          THEN  NEXT_DAY(qt_mth.start_date+level-1,'FRI')-7         --previous Friday 
     ELSE TO_CHAR(qt_mth.start_date+level-1,'DY')
    END),'yyyymmdd') date_id
  FROM 
    (SELECT TO_DATE(201401,'yyyymm') start_date    --TO_DATE(TRUNC(SYSDATE,'MON'))
     FROM DUAL) qt_mth
    ,DUAL
  CONNECT BY level <= TO_CHAR(LAST_DAY(qt_mth.start_date),'dd') 
 ),
 q_data_m2f AS                                     --This can be a data table
(SELECT 20140101 as tran_date_id, 261 val FROM DUAL
 UNION
 SELECT 20140102 as tran_date_id, 259 val FROM DUAL 
 UNION
 SELECT 20140103 as tran_date_id, 297 val FROM DUAL
)
SELECT qt_cal.*, q_data_m2f.val,
 SUM(q_data_m2f.val) OVER(ORDER BY qt_cal.mth_day) cumm_sum,
 ROUND((SUM(q_data_m2f.val)
  OVER(ORDER BY qt_cal.mth_day))/qt_cal.mth_day,3) cumm_avg
FROM qt_cal
LEFT JOIN  q_data_m2f
ON  q_data_m2f.tran_date_id = qt_cal.date_id 
;

SELECT * FROM v_all_201401_data
;