-- This is a query to create a Gregorian calendar summary by 
-- providing year as input (in four digit year format), 2014, 
-- 2015 etc.

WITH q_year AS (
 SELECT &i_yyyy c_year FROM DUAL
 ),
 q_yrsum AS (
  SELECT
    level mth_num,
    ADD_MONTHS(TRUNC(TO_DATE(c_year,'yyyy'),'yyyy'),level-1)
      mth_start, 
    LAST_DAY(ADD_MONTHS(TRUNC(TO_DATE(c_year,'yyyy'),'yyyy'),level-1)) 
      mth_end 
  FROM q_year
  CONNECT BY level <= 12
 )
 SELECT
  mth_num,
  mth_start,
  mth_end,
  mth_end+1-mth_start  mth_days,
  CEIL(mth_num/3)  quarter,
  SUM(mth_end+1-mth_start)  OVER (ORDER BY mth_num) ytd_days
FROM q_yrsum;

=> Gregorian Calendar Summary For - 2016