SQL Query that returns all days of a month

Early (?) morning today I received a call from a friend working in Mumbai as a PL/SQL developer. He was working overnight developing an SQL report and is stuck with an issue.

The requirement is to sum up values of a table based on date. The table had two relevent columns a date and an amount. Each date will have several entries. So the following select should do it.

select my_date, sum(amount)
from table1
where (month = current month)
group by my_date

The output will be

month_dates      Amount
-----------      ------
02-JUN-2009       1000
05-JUN-2009       4450  
...........
...........
...........
30-JUN-2009       2341 

You can see that 01-JUN-2009 , 03-JUN-2009 and 04-JUN-2009 is missing. This is because there are no corresponding entries in the table for these dates.However the requirement is that report output should print all the dates of a month irrespective of whether there is any entry in table1 or not. In the case with no entries amount should be printed as 0. So the ideal output will be

month_dates        Amount
-----------        ------
01-JUN-2009             0
02-JUN-2009          1000
03-JUN-2009             0 
04-JUN-2009             0
05-JUN-2009          4450 
...........
...........
...........
30-JUN-2009          2341 

So what he required is a second select statement which will return all the dates of a month as given below

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009

Then he can use this as an inline select statement as given below.

select all_days.my_date, sum(table1.amount)
from table1, (select_statement_2) all_days
where table1,my_date(+)=all_days.my_date
group by all_days.my_date

The output will be as expected

month_dates        Amount
-----------        ------
01-JUN-2009             0
02-JUN-2009          1000
03-JUN-2009             0  
...........
...........
...........
30-JUN-2009          2341 

So now the task is to write the select_statement_2 which will have as many rows as the number of days in the month and with one column which will have the dates in ascending order from the first day to last_day. So its time to play around with date functions again

For simplicity let us assume that we have to take the current month only.

To start with, I tried

SELECT TRUNC (SYSDATE, 'MON') month_dates
FROM DUAL

And this got me the first day of the month as following

month_dates
-----------

01-JUN-2009


Now I have to get a maximum of 31 rows which is the maximum number of days in a month. For the purpose I used a hierarchical query.


SELECT TRUNC (SYSDATE, 'MON')
FROM DUAL
CONNECT BY LEVEL <= 31

This gave me 31 rows with same date


month_dates
-----------

01-JUN-2009
01-JUN-2009
01-JUN-2009
...........
...........
01-JUN-2009


Now I incremented the date based on the rownum pseudo column


SELECT TRUNC (SYSDATE, 'MON') +rownum-1
FROM DUAL
CONNECT BY LEVEL <= 31

So the output now is

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009
01-JUL-2009

We are almost there, but the last row 01-JUL-2009 is the problem. The number of days in different months is different. It can be 28,29,30 or 31. So to correct this issue, I added an additional condition in the where clause which will ensure that the date printed will always belong to the current month.


SELECT TRUNC (SYSDATE, 'MON')+rownum-1
FROM DUAL
where to_char(SYSDATE,'MON')=to_char(TRUNC (SYSDATE, 'MON')+rownum-1,'MON')
CONNECT BY LEVEL <= 31

Now the last row is gone

month_dates
-----------

01-JUN-2009
02-JUN-2009
...........
...........
...........
30-JUN-2009

Now If I replace SYSDATE with any other date, all days of that month will be printed. To generalize, the following select statement will take a month and year as input and will query all dates of that month

SELECT TO_DATE ('01-' || '&Month' || '-' || '&year', 'DD-MON-YYYY')
+ LEVEL
- 1
FROM DUAL
CONNECT BY LEVEL <= 31
AND TO_CHAR ( TO_DATE ('01-' || '&Month' || '-' || '&year',
'DD-MON-YYYY')
+ LEVEL
- 1,
'MON') = '&Month'

For an input of Month-'FEB' and year '2008' the output will be

month_dates
-----------

01-02-2008
02-02-2008
..........
..........
29-02-2008

As I write this post my friend called me again to say that its working and I am a happy man.