display dates in a month

[cmuraz]

hi,
hope the query is simple, but i donno how to do.
i have some records in a table for the month may 2009 as
no date
---------------------------------------
1 02-may-09
4 10-may-09
13 12-may-09
20 24-may-09

only 4 days in may 09. but i want to display like
date no
-----------------------------------------
01-may-09 0
02-may-09 1
03-may-09 0
.
.
10-may-09 4
11-may-09 0
12-may-09 13
.
.
31-may-09 0

finally i need to display 31 days[all days ]in a month
how to do it in query. creating a view with this is needed for me.
pls help me..

thanks in advance..
muthu

[amitpatel66]

Try this:

Data in table1:

 
SQL> SELECT * FROM table1;
 
29-JUN-09    1
30-JUN-09    2
01-JUL-09    3
02-JUL-09    4
 
SQL> SELECT dt,no FROM
(SELECT dt,NVL(rn,0) no FROM
(select TRUNC(SYSDATE,'RRRR') + rn - 1 dt FROM
(SELECT rownum rn from all_objects where rownum <= DECODE(MOD(TO_CHAR(SYSDATE,'RRRR'),4),0,366,365))) x,
table1
WHERE TO_CHAR(x.dt,'DD-MON-RRRR') = (TO_CHAR(y.dat(+),'DD-MON-RRRR'))
AND TO_CHAR(dt,'MON-RRRR') IN (SELECT DISTINCT TO_CHAR(dat,'MON-RRRR') FROM table1))
ORDER BY dt