SQL Calculate Consecutive hours

Question

Need to calculate consecutive hours.

Here is the data

╔════╦══════════╦════════════╦═══════════╦═══════╗
║ ID ║ ClientID ║ Date       ║ From(Min) ║ To    ║  
╠════╬══════════╬════════════╬═══════════╬═══════╣
║ 101║ 2563     ║ 2020-06-19 ║ 360       ║ 1080  ║  
║ 102║ 2563     ║ 2020-06-19 ║ 1080      ║ 1140  ║ 
║ 103║ 2563     ║ 2020-06-19 ║ 1140      ║ 1200  ║  
║ 104║ 2561     ║ 2020-06-19 ║ 360       ║ 1080  ║  
║ 105║ 2563     ║ 2020-06-19 ║ 1200      ║ 1440  ║  
║ 106║ 2563     ║ 2020-06-20 ║ 0         ║ 60    ║  
║ 107║ 2561     ║ 2020-05-19 ║ 1080      ║ 1140  ║ 
║ 107║ 2563     ║ 2020-05-20 ║ 1080      ║ 1140  ║ 
╚════╩══════════╩════════════╩═══════════╩═══════╝

This the client has a limit of the amount of consecutive hours allowed.

Here is the result I'm looking for

╔══════════╦════════════╦═════════╦═════════╦═══════╦═══════════════════╗
║ ClientID ║ Date       ║ From    ║ To      ║ Hours ║ Consecutive Hours ║
╠══════════╬════════════╬═════════╬═════════╬═══════╣═══════════════════╣
║ 2563     ║ 2020-06-19 ║ 6:00am  ║ 6:00pm  ║ 12    ║ 12                ║
║ 2563     ║ 2020-06-19 ║ 6:00pm  ║ 7:00pm  ║ 1     ║ 13                ║
║ 2563     ║ 2020-06-19 ║ 7:00pm  ║ 8:00pm  ║ 1     ║ 14                ║
║ 2563     ║ 2020-06-19 ║ 8:00pm  ║ 12:00am ║ 4     ║ 18                ║
║ 2563     ║ 2020-06-20 ║ 12:00am ║ 1:00am  ║ 1     ║ 19                ║
║ 2563     ║ 2020-06-20 ║ 6:00pm  ║ 7:00pm  ║ 1     ║ 1                 ║
║ 2561     ║ 2020-06-19 ║ 6:00am  ║ 6:00pm  ║ 12    ║ 12                ║
║ 2561     ║ 2020-06-19 ║ 7:00pm  ║ 8:00pm  ║ 1     ║ 13                ║
╚══════════╩════════════╩═════════╩═════════╩═══════╩═══════════════════╝

Or a formula to calculate if client exceeds the allowed consecutive hours.

that was quick @DaleK, will post what I have tomorrow (from work). Thank You — monsey11, Jul 07 '20 at 03:47
Please verify if you have any typos in the sample data. The source data has dates in 2018, but result only in 2020. Also, what version of SQL Sever do you use? Please tag with the version. — Vladimir Baranov, Jul 07 '20 at 03:57
@monsey11, Overall, this looks like a [Packing Intervals](http://blogs.solidq.com/en/sqlserver/packing-intervals/) problem that Itzik Ben-Gan discussed in great detail in his article. He also shows there how to ignore gaps of up to a certain length. — Vladimir Baranov, Jul 07 '20 at 04:02
@VladimirBaranov you are right about the typos. SQL Server 2017 version 14.0.2027.2 — monsey11, Jul 07 '20 at 15:08

Gordon Linoff · Accepted Answer · 2020-07-07T15:57:33.297

1

This is a type of gaps-and-islands problem. Because you are dealing with minutes, it makes more sense to me to accumulate minutes rather than hours. You can divide by 60 to get hours:

select t.*,
       sum(tom - fromm) over (partition by clientid, date, grp order by fromm) as consecutive_minutes
from (select t.*,
             sum(case when prev_tom = fromm then 0 else 1 end) over (partition by clientid, date order by fromm) as grp
      from (select t.*,
                   dateadd(minute, fromm, date) as fromdt,
                   dateadd(minute, tom, date) as todt,
                   lag(tom) over (partition by clientid, date order by fromm) as prev_tom
            from t
          ) t
     ) t
order by clientid, date, fromm;

EDIT:

To handle counting hours across days is really just tweaking the above query:

select t.*,
       sum(tom - fromm) over (partition by clientid, grp order by date, fromm) as consecutive_minutes
from (select t.*,
             sum(case when prev_todt = fromdt then 0 else 1 end) over (partition by clientid order by date, fromm) as grp
      from (select t.*,
                   dateadd(minute, fromm, date) as fromdt,
                   dateadd(minute, tom, date) as todt,
                   lag(dateadd(minute, tom, date)) over (partition by clientid order by date, fromm) as prev_todt
            from t
          ) t
     ) t
order by clientid, date, fromm;

Here is a db<>fiddle.

edited Jul 07 '20 at 15:57

answered Jul 07 '20 at 12:33

Gordon Linoff

1,242,037
58
646
786

It only calculates within the same date. If hours overlap more than one day. – monsey11 Jul 07 '20 at 14:25
If hours overlap more than one day it does not add it to the consecutive_minutes. Date: 7/7/20 tom: 1440 really means 7/8/20 12am – monsey11 Jul 07 '20 at 14:32
The date field is a Date datatype not DateTime. needed to cast to datetime for the DATEADD function. – monsey11 Jul 07 '20 at 15:17
@monsey11 . . . The same basic query works with some tweaks. – Gordon Linoff Jul 07 '20 at 15:57
Works like a charm. Thank You. Will set as answer. Wondering if there is better option for performance. The user gets prompted as he enters the dates and times. Also need to ability to check large datasets. this is just part of many other validations it checks, so speed is particularly important. – monsey11 Jul 07 '20 at 16:13
@monsey11 . . . This should have pretty reasonable performance. It is only using window functions. – Gordon Linoff Jul 07 '20 at 16:16
Thank You @GordonLinoff. Is it easy to modify this statement to calculate consecutive days? – monsey11 Jul 07 '20 at 16:48
@monsey11 . . . If you can't divide by (24*60), then I would suggest asking a new question. – Gordon Linoff Jul 07 '20 at 16:49

SQL Calculate Consecutive hours

1 Answers1