Tags

,

This post shows how to create a calendar view using pure SQL.

E.g. September 2016 should look like this:

WEEK MON TUE WED THU FRI SAT SUN
---- --- --- --- --- --- --- ---
  35              01  02  03  04
  36  05  06  07  08  09  10  11
  37  12  13  14  15  16  17  18
  38  19  20  21  22  23  24  25
  39  26  27  28  29  30

To create this kind of calendar you can use recursive queries (aka connect by queries), to create a list of all days belonging to the current month.

SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
  FROM dual
CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE);

Having this list of all days the only additional thing we have to consider is how to group and format this data. I have choosen a DECODE approach to group the dates to the appropriate weekday column.

WITH days_of_curr_month AS (SELECT TRUNC(SYSDATE,'Month') + (ROWNUM - 1) AS theDate
                               FROM dual
                            CONNECT BY TRUNC(SYSDATE,'Month') + (ROWNUM - 1) <= LAST_DAY(SYSDATE))   
    ,data_formatter     AS (SELECT TO_CHAR(theDate,'IYYY')    AS Iyear                                   
                                  ,TO_CHAR(theDate,'IW')      AS Iweek                                   
                                  ,TO_CHAR(theDate,'DY','NLS_DATE_LANGUAGE=AMERICAN') AS MyDateDay                                   
                                  ,TO_CHAR(theDate,'DD')      AS MyDate                               
                               FROM days_of_curr_month) 
SELECT IWeek                               AS Week       
      ,MAX(DECODE(MyDateDay,'MON',MyDate)) AS MON       
      ,MAX(DECODE(MyDateDay,'TUE',MyDate)) AS TUE       
      ,MAX(DECODE(MyDateDay,'WED',MyDate)) AS WED       
      ,MAX(DECODE(MyDateDay,'THU',MyDate)) AS THU       
      ,MAX(DECODE(MyDateDay,'FRI',MyDate)) AS FRI       
      ,MAX(DECODE(MyDateDay,'SAT',MyDate)) AS SAT       
      ,MAX(DECODE(MyDateDay,'SUN',MyDate)) AS SUN 
  FROM data_formatter 
 GROUP BY IYear ,Iweek
 ORDER BY IYear ,IWeek;

The IYear information is needed as in the ISO-Calendar dates like January 1 – January 3 or December 29 – December 31 may belong to the previous or the following calendar year (week 53 or 1) and we have to make sure, that week 1 of the following year is placed at the end of the list and week 53 of the previous year is placed on top.