display dates in a month

cmuraz · #1 May 14th, 2009, 08:38 AM

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 · #2 June 29th, 2009, 10:55 AM

Try this:

Data in table1:

Expand|Select|Wrap|Line Numbers

  
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

display dates in a month

re: display dates in a month

/bytes/development

/bytes/it

/bytes/about