Modeling a calendar using SQL

Although it is possible to hardcode a calendar using a PL/SQL block, it can look a bit complicated at first sight.

Now, let’s look at the monthly calendar definition using SQL. For simplicity, we will use the month extracted from the sysdate value, but generally, it can be applied to any specified DATE value. Note that the processing can be optimized by grouping multiple select statements together. However, for explanatory purposes, step-by-step processing is more convenient. Therefore, individual steps are nested. Each step takes the data from the preceding processing.

Step 1 – Getting the day numbers for each day in the month

The goal of this step is to get the same number of rows as the number of days in the month. To do that, the dual table is referenced, which holds just one row. Therefore, the CONNECT BY LEVEL clause is used with the value expressing the numerical representation of the difference between the last day of the...