Home » SQL & PL/SQL » SQL & PL/SQL » how can i get missing dates
how can i get missing dates [message #285070] Mon, 03 December 2007 05:16 Go to next message
selvan1310
Messages: 1
Registered: August 2007
Location: chennai
Junior Member
hai gurus

i have table like this
name dat value
xx 20-nov-2007 4
yy 21-nov-2007 5
aa 22-nov-2007 3
bb 26-nov-2007 1

i want the result, display like this
xx 20-nov-2007 4
yy 21-nov-2007 5
aa 22-nov-2007 3
23-nov-2007 0
24-nov-2007 0
25-nov-2007 0
bb 26-nov-2007 1

ie. the non entered date field column values are zero in the output

pl. give a query for this

thanks
Re: how can i get missing dates [message #285072 is a reply to message #285070] Mon, 03 December 2007 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search here for calendar, this has already been answered several times.

Regards
Michel
Re: how can i get missing dates [message #285373 is a reply to message #285070] Tue, 04 December 2007 05:42 Go to previous messageGo to next message
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
Member
A calendar query :

SELECT TO_DATE('01-01-07','DD-MM-RR') + L DD
FROM
(
SELECT LEVEL L
FROM DUAL
CONNECT BY LEVEL < 1000
) CAL

--------

Make an outer join between your table and this CAL
Re: how can i get missing dates [message #285376 is a reply to message #285373] Tue, 04 December 2007 05:45 Go to previous message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel

Previous Topic: Packege
Next Topic: help~~problem of Trigger
Goto Forum:
  


Current Time: Tue Aug 22 00:07:32 CDT 2017

Total time taken to generate the page: 0.09886 seconds