Re: SQL to have a row for every single day

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 21 Mar 2003 07:25:25 -0800
Message-ID: <45a06b65.0303210725.a001297@posting.google.com>

"Luch" <optimaljedi_at_nospam.com> wrote in message news:<Lgxea.11833$jA2.1067886_at_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?

I don't think the OP doesn't know how to join, I think the problem is how to generate the table as if it had all the required dates on on the fly, something like this.

SQL> select * from t order by d;

D                  N
--------- ----------
13-MAR-03          2
17-MAR-03          3
21-MAR-03          1

SQL> select a.d, t.n from t,
  2      (select d1 + rownum - 1 d
  3      from all_objects,
  4      (select min(d) d1, max(d) - min(d) + 1 dn from t)
  5      where rownum <= dn) a

  6 where a.d = t.d (+)
  7 order by a.d;

D                  N
--------- ----------
13-MAR-03          2
14-MAR-03
15-MAR-03
16-MAR-03
17-MAR-03          3
18-MAR-03
19-MAR-03
20-MAR-03
21-MAR-03          1

9 rows selected.

In this example you basically need a dummy table, in this case all_objects, that you know has as many rows as the number of days between your start and end dates. You then use an inline view to get start date (d1) and number of days (dn) of your date range. You use rownum from your dummy table select with start date from the range view to generate all the dates in the range. Then you can outer join from the generated dates back to your original table.

Performance may not be very good for a large number of rows. Also in this example all the dates in table t were trunc'ed to zero out the time component and so make it equal for all days. If the times differ in the source table the trunc will need to be added into the query to ensure the outer join finds the required matches.

Hth
Martin Received on Fri Mar 21 2003 - 09:25:25 CST