Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    Unanswered: Find date sequence gaps

    I have a table with the field:

    datesold

    The dates are not in chronological order. Using only a query (without perl etc) I would like to traverse the dates returning a list of beginning and ending dates that border a date gap greater than some value of days.

    version 4.1

    Any help much appreciated.

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by radiohead
    I would like to traverse the dates returning a list of beginning and ending dates that border a date gap greater than some value of days.
    Code:
    SELECT
      f1.datesold AS d1,
      (SELECT MIN(f2.datesold) FROM foo f2 WHERE f2.datesold > f1.datesold) AS d2
    FROM foo f1
    HAVING d1 < d2 - INTERVAL 5 DAY
    ORDER BY d1;
    --
    felix

  3. #3
    Join Date
    Jul 2005
    Posts
    5
    The query works very well, thanks. I added a distinct to prevent redundant checking (actually I dont know if it prevents redundant checking, but at least removes them from display):

    SELECT
    distinct(f1.datesold) AS d1,
    (SELECT MIN(f2.datesold) FROM foo f2 WHERE f2.datesold > f1.datesold) AS d2
    FROM foo f1
    HAVING d1 < d2 - INTERVAL 5 DAY
    ORDER BY d1;

    Is there anyway to optimize the query to run quicker? It takes minutes to run it on a table with 100,000 entries, maybe 2500 unique dates.

    Thanks again.

  4. #4
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by radiohead
    Is there anyway to optimize the query to run quicker? It takes minutes to run it on a table with 100,000 entries, maybe 2500 unique dates.
    Do you have an index on datesold?

    --
    felix

Posting Permissions

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