Find date sequence gaps

radiohead · #1 (**permalink**) 12-02-05, 14:56

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.

felixg · #2 (**permalink**) 12-05-05, 05:20

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

radiohead · #3 (**permalink**) 12-06-05, 12:01

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.