Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL to have a row for every single day

SQL to have a row for every single day

From: Luch <optimaljedi_at_nospam.com>
Date: Fri, 21 Mar 2003 05:18:35 GMT
Message-ID: <Lgxea.11833$jA2.1067886@newsread2.prod.itd.earthlink.net>


I would like to have a resultset that has every day in a certain range appear at least once, in conjuction with my table called t_history which will have records for some days.

For example:
Table t_history has fields mydate(date) and count(integer).

t_history has these records:

March 10, 2003        5
March 13, 2003        3
March 15, 2003        1


I'd like to use a SQL with a range of March 10-15 and have the following resultset returned:

March 10, 2003        5
March 11, 2003        0
March 12, 2003        0
March 13, 2003        3
March 14, 2003        0
March 15, 2003        1

The zero's can be nulls as well, I don't care.

In a way, what I would like is an outer join to a table that had a record for every single day. Any ideas how I can go about this?

--
Luch
replace nospam with hotmail for e-mail.
Received on Thu Mar 20 2003 - 23:18:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US