# 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.

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.*,
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.*,