SQL Calculate Consecutive hours [closed]

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.

Answer

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.