SQL finding total flagged hours within first 8 hours worked

Question

I am aiming to find total hours worked in a day for shifts or fractions of shifts per person that are tagged with a value and fall within the first 8 hours of work, excluding breaks, on any day. Then display tagged shifts along with eligible total value.

Example

• 2am - 4am (2 hrs) - Normal shift
• 5am - 9am (4 hrs) - Tagged shift
• 10am - 3pm (5 hrs) - Tagged shift

Eligible Total 4 hrs (5am - 9am) + 2 hrs (10am - 12pm)

Source Table Format

PersonID | WorkDate | StartTime | FinishTime | HoursWorked (pre calculated) | ShiftType

DECLARE @Table TABLE
(
    PersonID INT
    , WorkDate DATETIME
    , StartTime DATETIME
    , FinishTime DATETIME
    , HoursWorked DECIMAL(4, 2)
    , ShiftType VARCHAR(50)

);

INSERT INTO @Table VALUES (100,'2019-11-26','1900-01-01T02:00:00', '1900-01-01T04:00:00',2,'Normal')
INSERT INTO @Table VALUES (100,'2019-11-26','1900-01-01T05:00:00', '1900-01-01T09:00:00',4,'Tagged')
INSERT INTO @Table VALUES (100,'2019-11-26','1900-01-01T10:00:00', '1900-01-01T15:00:00',5,'Tagged')

Result Set

+----------+------------+---------------------+---------------------+------------------------------+-----------+---------------+
| PersonID | WorkDate   | StartTime           | FinishTime          | HoursWorked (pre calculated) | ShiftType | EligibleHours |
+----------+------------+---------------------+---------------------+------------------------------+-----------+---------------+
| 100      | 2019-11-26 | 1900-01-01T05:00:00 | 1900-01-01T09:00:00 | 4                            | Tagged    | 4             |
+----------+------------+---------------------+---------------------+------------------------------+-----------+---------------+
| 100      | 2019-11-26 | 1900-01-01T10:00:00 | 1900-01-01T15:00:00 | 5                            | Tagged    | 2             |
+----------+------------+---------------------+---------------------+------------------------------+-----------+---------------+

Answer 1

Here's my understanding of the requirements:

• Collect the first 8 hours of work done by each user on each day
• If a shift starts before 8 hours and finishes after 8 hours, it should be marked with the number of hours that occurred before the user got to 8 hours
• Filter out all untagged shifts
• Filter out all shifts without eligible hours

To solve I used two windowing functions:

• sum(TotalHours) over (...) to determine the cumulative sum of how many hours were worked in the current shift and all previous
• (8 - lag(CumulativeWork, 1, 0)) over (...) to determine how much eligibility was left entering into the current shift.

Here's the code:

select 
  PersonID,
  WorkDate,
  StartTime,
  FinishTime,
  HoursWorked,
  ShiftType,
  case 
    when RemainingWork <= HoursWorked then RemainingWork
    when RemainingWork > HoursWorked then HoursWorked 
    else 0 end as EligibleWork
from 
(
  select 
  -- Calculate how much eligible work can happen in a given shift by 
  -- subtracting the amount of work done in previous shifts from 8
  8 - lag (CumulativeWork, 1, 0) over (Partition by PersonID, WorkDate order by StartTime) as RemainingWork
  , *
  from (
    select 
    -- Create a cumulative sum of the hours worked
    sum(HoursWorked) over (Partition by PersonID, WorkDate order by StartTime) as CumulativeWork
    , *
    from ShiftTable
  ) a
) b
where shiftType = 'Tagged' and remainingWork > 0

And the fiddle: http://sqlfiddle.com/#!18/7a8dd/12