Home » SQL & PL/SQL » SQL & PL/SQL » how to fill gap between two dates
how to fill gap between two dates [message #223853] Mon, 12 March 2007 01:55 Go to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi all
i need solution for my two diff requirements.

[QUOTE]
SQL> desc tempt ;
Name Null? Type
----------------------------------------- -------- ----------
NUM NUMBER(3)
FD DATE
LD DATE

SQL> select * from tempt ;

NUM FD LD
---------- --------- ---------
10 04-MAR-07 08-MAR-07
10 11-MAR-07 11-MAR-07
10 20-MAR-07 23-MAR-07
20 20-MAR-07 23-MAR-07

Requirements :
1. query should display all missing dates between FD and LD

OUTPUT :

NUM ALL_DATE
------ ---------
10 04-MAR-07
10 05-MAR-07
10 06-MAR-07
10 07-MAR-07
10 08-MAR-07
10 11-MAR-07
10 20-MAR-07
10 21-MAR-07
10 22-MAR-07
10 23-MAR-07
20 20-MAR-07
20 21-MAR-07
20 22-MAR-07
20 23-MAR-07

2. query should display all missing date comma seperated for each NUM

OUTPUT :

NUM DATE_SEP_COMMA
------ ---------
10 04-MAR-07, 05-MAR-07, 06-MAR-07, 07-MAR-07 , 08-MAR-07,11-MAR-07,20-MAR-07,21-MAR-07, 22-MAR-07 ,23-MAR-07
20 20-MAR-07, 21-MAR-07 ,22-MAR-07,23-MAR-07

I am using oracle 9i.
Looking for your best solution for my req. It will be great if you provide the diff approch to get the same result as shown above.

Thanks in Advance
Pravin


OTE]
Re: how to fill gap between two dates [message #224066 is a reply to message #223853] Mon, 12 March 2007 17:17 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3225867788098

http://tkyte.blogspot.com/2006/08/evolution.html

Previous Topic: cursors
Next Topic: package query
Goto Forum:
  


Current Time: Sat Dec 10 22:31:28 CST 2016

Total time taken to generate the page: 0.09518 seconds