Analytic Functions In Calendar Summary
-- NTILE(4) function works correctly to compute four quarters, since number of
-- months are 12 (level <= 12) and divided into four data groups.
-- For number of months less than 12, use Quarter Query 
-- to get exact quarter based on month.

SELECT  level month_no,
  NTILE(4) OVER (ORDER BY level) As Quarter,  
  TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1),'mm/dd/yyyy') start_date,  
  TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)),'mm/dd/yyyy') end_date,  
  LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1))-  
           ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)+1 month_days,  
  SUM(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1))  
              - ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)+1)  
      OVER (ORDER BY level) ytd_days  
FROM DUAL CONNECT BY level <= 12;  

Analytic Functions In Calendar Summary


Refer: Oracle ERP - GL Calendar

Oracle registered trademark of Oracle Corporation.

Last Revised on: July 31, 2014

  7610