At 9:14 -0500 4/4/02, George Smith wrote:
>I have a mysql table like this
>mysql> describe reports;
>+---------+---------+------+-----+------------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+---------+---------+------+-----+------------+-------+
>| rptdate | date | | PRI | 0000-00-00 | |
>| id | int(11) | | PRI | 0 | |
>| summary | text | | | | |
>+---------+---------+------+-----+------------+-------+
>
>I can easily find what dates a given id has made a summary report
>using a query like
>mysql> select rptdate from reports where id=123 and
> -> rptdate between '1998-01-01' and '2000-12-31';
>+------------+
>| rptdate |
>+------------+
>| 1998-01-02 |
>| 1998-01-04 |
><many rows omitted>
>| 2000-12-30 |
>| 2000-12-31 |
>+------------+
>
>My question is how to write a query that finds the dates that
>aren't in the database. For example, for the same range as
>the sample, the first two items of interest would be
>'1998-01-01' and '1998-01-03'.
You need to create another table that has a row for each date in the
range, then use that table in a LEFT JOIN to your original table
to find rows in the date table that are not represented by your original
table.
I happen to have a DBI script make_date_list.pl that creates just
such a table. Contact me offline if you'd like a copy.
>I looked in the archive and I saw the recent discussions using
>a join to find data that is in one table and not another. I
>don't think that is applicable as I don't have a table of
>all possible dates to compare with.
>
>I see two solutions -- either create a temporary table containing
>all possible dates in my range of interest, then using the
>techniques in the archive, or get the list of dates with a
>report present and then use an external program to find
>the missing ones.
>
>Is there a better way than these two? What would be the most
>efficient method? All advice would be appreciated.
>
>Thanks,
>George
