Finding longest sequence in SQL

Question

If I have a table which contains dates, eg (in year-month-day then time format):

2015-06-22 12:39:11.257
2015-06-22 15:44:46.790
2015-06-22 15:48:50.583
2015-06-23 08:25:50.060
2015-07-01 07:11:37.037
2015-07-07 13:40:11.997
2015-07-08 13:12:08.723
2015-07-08 13:12:13.900
2015-07-08 13:12:16.010
2015-07-10 12:29:59.777
2015-07-13 15:42:49.077
2015-07-13 15:47:48.670
2015-07-13 15:47:51.547
2015-07-14 08:11:53.023
2015-07-14 08:14:21.243
2015-07-14 08:16:49.410
2015-07-14 08:17:11.997
2015-07-14 09:58:28.840
2015-07-14 09:59:34.640
2015-07-15 15:39:39.993
2015-07-17 08:45:20.157
2015-07-24 14:00:00.487
2015-07-24 14:03:53.773
2015-07-24 14:12:41.717
2015-07-24 14:13:33.957
2015-07-24 14:15:40.953
2015-08-25 12:43:03.920

... is there a way (in SQL) that I can find the longest unbroken sequence of days. I just need the total number of days. So in the above, there are entries for 22nd June and 23rd of June, so the sequence there is 2 days. There's also entries for 13th July, 14th July, and 15th July; this is the longest sequence - 3 days. I don't care about the time portion, so an entry just before midnight, then an entry just after would count as 2 days.

So I want some SQL that can look at the table, and return the value 3 for the above.

It's a simple matter of programming. What have you tried? You can definitely do it with a cursor. You might be able to do it with a recursive cte. You might also be able to do it by joining to a date table. — Tab Alleman
– Tab Alleman, Commented Aug 27, 2015 at 14:38
I didn't wan't to do any programming or cursors, I thought I could do something like a modified version of stackoverflow.com/questions/15368801/… — David_001
– David_001, Commented Aug 27, 2015 at 15:04
You might want to include those details in your question next time then. — Tab Alleman
– Tab Alleman, Commented Aug 27, 2015 at 15:08

Sean Lange · Accepted Answer · 2015-08-27 14:49:26Z

No need for a cursor or any type of recursion to solve this. You can do this using a gaps and islands technique. This produces the desired output from your sample data.

with SomeDates as
(
    select cast('2015-06-22 12:39:11.257' as datetime) as MyDate union all
    select '2015-06-22 15:44:46.790' union all
    select '2015-06-22 15:48:50.583' union all
    select '2015-06-23 08:25:50.060' union all
    select '2015-07-01 07:11:37.037' union all
    select '2015-07-07 13:40:11.997' union all
    select '2015-07-08 13:12:08.723' union all
    select '2015-07-08 13:12:13.900' union all
    select '2015-07-08 13:12:16.010' union all
    select '2015-07-10 12:29:59.777' union all
    select '2015-07-13 15:42:49.077' union all
    select '2015-07-13 15:47:48.670' union all
    select '2015-07-13 15:47:51.547' union all
    select '2015-07-14 08:11:53.023' union all
    select '2015-07-14 08:14:21.243' union all
    select '2015-07-14 08:16:49.410' union all
    select '2015-07-14 08:17:11.997' union all
    select '2015-07-14 09:58:28.840' union all
    select '2015-07-14 09:59:34.640' union all
    select '2015-07-15 15:39:39.993' union all
    select '2015-07-17 08:45:20.157' union all
    select '2015-07-24 14:00:00.487' union all
    select '2015-07-24 14:03:53.773' union all
    select '2015-07-24 14:12:41.717' union all
    select '2015-07-24 14:13:33.957' union all
    select '2015-07-24 14:15:40.953' union all
    select '2015-08-25 12:43:03.920'
)
, GroupedDates as
(
    select cast(MyDate as DATE) as MyDate
        , DATEADD(day, - ROW_NUMBER() over (Order by cast(MyDate as DATE)), cast(MyDate as DATE)) as DateGroup
    from SomeDates
    group by cast(MyDate as DATE)
)
, SortedDates as
(
    select DATEDIFF(day, min(MyDate), MAX(MyDate)) + 1 as GroupCount
        , min(MyDate) as StartDate
        , MAX(MyDate) as EndDate
    from GroupedDates
    group by DateGroup  
)

select top 1 GroupCount
    , StartDate
    , EndDate
from SortedDates
order by GroupCount desc

Florin Ghita · Accepted Answer · 2015-08-28 06:08:40Z

1

The input here is, in fact:

select trunc(date_column,'DD') day
from your_table
group by trunc(date_column,'DD');

From this point I can consider dates as numbers to input more easier the data and your problem is to find longest consecutive sequence.

so, an input table:

create table a(
col integer);

insert into a values (1);
insert into a values (2);
insert into a values (4);
insert into a values (5);
insert into a values (6);
insert into a values (8);
insert into a values (9);
insert into a values (11);
insert into a values (13);
insert into a values (14);
insert into a values (17);

and with this query you will get the longest sequence starting from every line:

with s(col, i) as (
  select col, 1 i from a
  union all
  select a.col, i + 1
  from s join a on s.col = a.col+1
  )
  --select * from s
  select col, max(i) 
  from s 
  group by col
  order by col
  ;

Result:

From this point you can easily select the maximum. Also, for dates you can use dateadd(dd,1,date_column).

The explanation of recursive CTE: For every row I will find (if exists) the next row and increment the column i. The recursion exits when there are no "next" line.

OBS: I believe the code can be improved, but you got the ideea.

SQLFIDDLE

UPDATE To improve performance and keep using recursivity we can start only from numbers that doesn't have a prior consecutive number.

with p as (
  select * from (
    select col, coalesce(col - (lag(col) over (order by col)),2) as has_prev 
    from a
    ) b
  where has_prev != 1
),
 s(col, i) as (
  select col, 1 i from p
  union all
  select s.col, i + 1
  from s join a on s.col+i = a.col
  )
  --select * from p
  select col, max(i) 
  from s 
  group by col
  order by col
  ;

SQLFIDDLE2

edited Aug 28, 2015 at 6:08

answered Aug 27, 2015 at 16:08

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

3 Comments

Sean Lange Over a year ago

The issue with this is performance. You are using a recursive cte to count which is effectively row by row processing. sqlservercentral.com/articles/T-SQL/74118 Also, I demonstrated how this can be solved with no recursion required.

Florin Ghita Over a year ago

You are right and i upvoted your answer. I know that a better designed solution is 100% better than another which is poorer by design/algorithm. But just for the sake of to be another solution, I've improved it a bit :))

David_001 Over a year ago

Thanks for providing this, a recursive CTE is what I tried first but couldn't get to work quite right, so it's useful to have this as a reference.

Collectives™ on Stack Overflow

Finding longest sequence in SQL

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related