List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 4 2002 2:44pm
Subject:Re: Finding missing items in a series
View as plain text  
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


Thread
Finding missing items in a seriesGeorge Smith4 Apr
  • Re: Finding missing items in a seriesPaul DuBois4 Apr
Re: Finding missing items in a seriesGeorge Smith5 Apr