SQL Count Consecutive Days per line

Question

Looking to get the "Total Consecutive Days" per row.

Shift Table Contains ShiftId, ClientID, ServiceId and ProviderID ShiftDetails Table has the dates.

There can be multiple Shifts with the same ShiftId, ClientID, ServiceId There can even be duplicate dates.

Would like to have multiple "Total Consecutive Days" calculations. Consecutive Days Per ClientID Consecutive Days Per ClientID and ServiceID Consecutive Days Per ClientID , ServiceID and ProviderID.

SQL Fiddle

score 1 · Answer 1 · answered Aug 04 '20 at 18:10

1

This is a gaps-and-islands problem. You can identify the islands by subtracting a sequence of integers from the date -- constant differences identify adjacent dates. Then use count(*) as a window function:

select t.*,
       count(*) over (partition by clientid, serviceid, dateadd(day, -seqnum, date)) as consecutive_days
from (select t.*,
             row_number() over (partition by clientid, serviceid order by date) as seqnum
      from t
     ) t

answered Aug 04 '20 at 18:10

Gordon Linoff

1,242,037
58
646
786

tried to convert it to my data. it's not returning the desired results. added query to question. – monsey11 Aug 04 '20 at 20:57
@monsey11 . . . Set up a db fiddle. – Gordon Linoff Aug 04 '20 at 21:10
Here http://sqlfiddle.com/#!18/8861e5/1 @/gordon-linoff – monsey11 Aug 05 '20 at 15:54

SQL Count Consecutive Days per line

1 Answers1