Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: query to display gaps in dates

    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

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    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.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by gvee View Post
    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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •