Re: SQL to have a row for every single day

"Luch" wrote...
> 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.

Hi Luch,

sorry, I don't see the problem. Let's have a guess: 1. You don't have a table with a row for every day in a period?

    Well, get one by using a PL/SQL loop. 2. You have such a table but don't know how to join?

    Sounds like homework. Will be something like     SELECT tabledat.dat, sum(val) FROM tabledat, tablevalue     WHERE tabledat.dat = tablevalue.dat(+)     GROUP BY tabledat.dat;
    You will have to use the TRUNC function on the date columns.     To show '0' instead of NULL, use the NVL function on the SUM.

If my assumptions are wrong, give some more details.

hth,
Guido Received on Fri Mar 21 2003 - 01:21:02 CST