query to display gaps in dates

dbfmhn · #1 (**permalink**) 10-29-09, 23:51

Is there a way (for sql-lite) to report gaps in dates, that is, records that have a date field where dates are skipped?

The result set would just be a list of dates that do not exist in the table, or better yet, a date range of skipped dates. EG

start end
_________ __________
2009-01-22 2009-01-27 '6 days
2009-02-11 2009-02-13 '1 day

means that there are no records with dates in the above date ranges.

To get even fancier, it would be nice to omit Sat and Sun. The function strftime('%w', date) returns the weekday value (0-6).

The table is "reports" the date field is "date"

Thanks mucho

scooby_at_work · #2 (**permalink**) 11-03-09, 15:21

It's always a little tricky to ask a SQL database about stuff that doesn't exist. It's also tricky to ask a SQL database about the difference between rows.

You'll need to find all the rows in datePool such that date + 1 doesn't exist, and you need the extent of the gap:

Code:

select distinct o.dateCol + 1 as start_dt, 
  (select min(i.dateCol) from yourTable i where i.dateCol > o.dateCol) - 1 
as end_dt, end_dt - start_dt + 1 as count
from yourTable o where end_dt > o.dateCol + 1

(i refers to the "inner" version of a table, and o to the "outer")

I'm not a big SQLite user, so I'm not sure it will handle all these subqueries, but I think it's pretty flexible. You may need to use a function to add or subtract days.

Also, if yourTable guarantees that each date is only represented once (if that column is UNIQUE or PRIMARY KEY), you can drop the 'distinct' keyword. Either way, I'd recommend an index on that column.

gvee · #3 (**permalink**) 11-03-09, 19:44

Your best option is to have a calendar table!

Then perform a simple outer join to find the dates that do not have a corresponding record

Code:

CREATE TABLE calendar (
   date_field datetime
)

/* Fill the table with data */

SELECT calendar.date_field
FROM   calendar
 LEFT
  JOIN your_table
    ON your_table.date_field = calendar.date_field
WHERE  your_table.date_field IS NULL

Peter.Vanroose · #4 (**permalink**) 11-11-09, 18:12

Quote:

Originally Posted by gvee

Your best option is to have a calendar table!

That would indeed also be my preferred path.
Some database systems support recursive SQL to generate such a table "on the fly". Most others will support it in the near future.
With recursive SQL, the query becomes:

Code:

WITH calendar (date_field) AS
RECURSIVE (SELECT CAST('2009-01-01' AS datetime) AS date_field
           UNION ALL
           SELECT date_field + 1
           FROM   calendar
           WHERE date_field < CAST('2009-12-31' AS datetime)
          )
SELECT calendar.date_field
FROM   calendar
       LEFT OUTER JOIN your_table
       ON your_table.date_field = calendar.date_field
WHERE  your_table.date_field IS NULL

(or alternatively, with a NOT EXISTS or an EXCEPT).

Internet Services

Web Development

Digital Marketing

Consumer Tech