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: 64153
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: 64153
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: decode doubt
Next Topic: Schema level Trigger on DML operations
Goto Forum:
  


Current Time: Sun Dec 11 08:31:51 CST 2016

Total time taken to generate the page: 0.08915 seconds