On building SQL

I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL.

You can see the question and the answer here

But I thought it might be worth explaining the process.  Of course, the way I approach the problem might be totally different to the way others would, and that’s fine.  But for the novice, you might get something useful out of this.

So here is January 2016.

image

The first thing I’m thinking to print a calendar is

  • I will need to know the number of days in the month
  • I will need to know what day the month starts on
  • I will need to segment the data into weeks

So let’s tackle them.

Number of days in the month

I can use LAST_DAY for that.


SQL> select last_day(date '2016-01-16') end_of_month from dual;

END_OF_MO
---------
31-JAN-16

SQL>
SQL> select to_char(last_day(date '2016-01-16'),'DD') days_in_month from dual;

DA
--
31

and because I will be printing 31 days, I will need to generate 31 days worth of rows. Taking the above, I can use the familiar ‘connect by’ trick to generate my date


SQL> select rownum d
  2  from dual
  3  connect by level <= to_number(to_char(last_day(date '2016-01-16'),'DD'));

         D
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31

31 rows selected.

So there are my days, now I just need to jiggle them around so they look like a calendar Smile

Next I need to know when the month starts.  First I truncate the date down to the month level


SQL> select trunc(date '2016-01-16','MM') mth_start from dual;

MTH_START
---------
01-JAN-16

and then I want to know what day of the week it commences on


SQL> select to_number(to_char(trunc(date '2016-01-16','MM'),'D')) starts_on from dual;

 STARTS_ON
----------
         6

My first attempt at working out a week number is then simply to divide by 7 and truncate. I’ve just substituted the explicit first of month literal here for simplicity. We’ll put the full expression from above back in later.


SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-1+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3  from dual
  4  connect by level <= 31
  5  /

DY          WEEK_NUM
--------- ----------
01-JAN-16          0
02-JAN-16          1
03-JAN-16          1
04-JAN-16          1
05-JAN-16          1
06-JAN-16          1
07-JAN-16          1
08-JAN-16          1
09-JAN-16          2
10-JAN-16          2
11-JAN-16          2
...

Now I compare that to my true calendar – I see that the 3rd starts on a Sunday, whereas my basic trunc would have the 2nd being Sunday.  So I need to offset things by 1, and try again



SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3  from dual
  4  connect by level <= 31;

DY          WEEK_NUM
--------- ----------
01-JAN-16          0
02-JAN-16          0
03-JAN-16          1
04-JAN-16          1
05-JAN-16          1
06-JAN-16          1
07-JAN-16          1
08-JAN-16          1
09-JAN-16          1
10-JAN-16          2
11-JAN-16          2
12-JAN-16          2
13-JAN-16          2
...

That looks better. Now I can also add in the day of the week



SQL> select date '2016-01-01'+ rownum -1 dy,
  2         trunc((rownum-2+to_number(to_char(date '2016-01-01','D')))/7) week_num
  3         to_char(trunc(date '2016-01-01','MM') -1 + rownum,'D') dow
  4  from dual
  5  connect by level <= 31;

DY          WEEK_NUM D
--------- ---------- -
01-JAN-16          0 6
02-JAN-16          0 7
03-JAN-16          1 1
04-JAN-16          1 2
05-JAN-16          1 3
06-JAN-16          1 4
07-JAN-16          1 5
08-JAN-16          1 6
09-JAN-16          1 7
10-JAN-16          2 1
11-JAN-16          2 2
12-JAN-16          2 3

And we are pretty close to being complete. We can now use PIVOT or the common DECODE expression to flip the rows into columns. And hence we arrive at this



SQL> select
  2   max(decode(dow,1,d,null)) Sun,
  3   max(decode(dow,2,d,null)) Mon,
  4   max(decode(dow,3,d,null)) Tue,
  5   max(decode(dow,4,d,null)) Wed,
  6   max(decode(dow,5,d,null)) Thu,
  7   max(decode(dow,6,d,null)) Fri,
  8   max(decode(dow,7,d,null)) Sat
  9  from
 10   ( select rownum d
 11            ,rownum-2+to_number(to_char(date '2016-01-01','D')) p
 12            ,to_char(date '2016-01-01' -1 + rownum,'D') dow
 13     from dual
 14     connect by level <= 31
 15    )
 16  group by trunc(p/7)
 17  order by trunc(p/7);

       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                1          2
         3          4          5          6          7          8          9
        10         11         12         13         14         15         16
        17         18         19         20         21         22         23
        24         25         26         27         28         29         30
        31


Finally, we put back our original expressions so that we can accept any date of the month, and work from that. Hence our complete solution becomes



SQL> define my_date = 12-JAN-16
SQL>
SQL> select
  2   max(decode(dow,1,d,null)) Sun,
  3   max(decode(dow,2,d,null)) Mon,
  4   max(decode(dow,3,d,null)) Tue,
  5   max(decode(dow,4,d,null)) Wed,
  6   max(decode(dow,5,d,null)) Thu,
  7   max(decode(dow,6,d,null)) Fri,
  8   max(decode(dow,7,d,null)) Sat
  9  from
 10   ( select rownum d
 11            ,rownum-2+to_number(to_char(trunc(to_date('&my_date'),'MM'),'D')) p
 12            ,to_char(trunc(to_date('&my_date'),'MM') -1 + rownum,'D') dow
 13     from dual
 14     connect by level <=
 15        to_number(to_char(last_day(to_date('&my_date')),'DD'))
 16    )
 17  group by trunc(p/7)
 18  order by trunc(p/7);

       SUN        MON        TUE        WED        THU        FRI        SAT
---------- ---------- ---------- ---------- ---------- ---------- ----------
                                                                1          2
         3          4          5          6          7          8          9
        10         11         12         13         14         15         16
        17         18         19         20         21         22         23
        24         25         26         27         28         29         30
        31

and we’re done. I hope you found this useful.

One thought on “On building SQL

  1. Interesting writing , I was fascinated by the facts , Does someone know if my business could possibly locate a fillable DA 31 copy to complete ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s