Gregorian Calendar - Quarters
-- Query to get quarters of a year in Gregorian Calendar 
-- format using Oracle database functions.

WITH q_year AS
(SELECT TRUNC(TO_DATE(&i_yyyy,'yyyy'),'yyyy') first_day
 FROM  DUAL),
q_cal AS (
 SELECT 
  first_day,
  LAST_DAY(ADD_MONTHS(first_day,11)) last_day,
  LAST_DAY(ADD_MONTHS(first_day,11))+1 - first_day days_of_yr 
 FROM q_year),
q_ydys AS (
 SELECT
  first_day+level-1 cal_day,
  LAST_DAY(first_day+level-1) last_day,
  to_number(to_char(first_day+level-1,'mm')) month_no
  --, days_of_yr
 FROM q_cal
  CONNECT BY level <= days_of_yr),
q_all AS (
 SELECT
   cal_day,
   last_day,
   month_no,
   DECODE(GREATEST(month_no,4), month_no,
    DECODE(GREATEST(month_no,7), month_no,
     DECODE(GREATEST(month_no,10), month_no,4,3),2),1)  
   qtr
 FROM  q_ydys)
SELECT
 MIN(cal_day) qtr_start, MAX(cal_day) qtr_end, qtr
FROM q_all
GROUP BY qtr
ORDER BY qtr
;

=> Gregorian Calendar - 2014 Quarters Output

Calendar Summary Query

Yearly Quarter Query

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 30th, 2014

  275