Group durations in buckets for entire query date range, then display by date

Question

I have a set of data that provides durations of overtime taken by a user by date.

I need to further split this up in this way:

• Overtime up to and including 5 hours for the entire date range in 1 column
• Overtime after the initial 5 hours for the entire date range in another column

Because the data needs to be displayed by day, I'm having a hard time achieving this.

The data I'm working with looks like this:

SELECT 
    USERNAME,
    DATE,
    SUM (DATEDIFF(SECOND, STARTTIME, ENDTIME) / 3600.0) AS OT_DURATION
FROM EVENTS
WHERE EVENT_TYPE = 'OVERTIME'
AND DATE BETWEEN '2021-01-25' AND '2021-01-31'
;

Current output:

| USERNAME | DATE       | OT_DURATION |
|----------|------------|-------------|
| bob      | 2021-01-25 | 2.0         |
| bob      | 2021-01-26 | 2.0         |
| bob      | 2021-01-27 | 3.0         |
| bob      | 2021-01-28 | 2.0         |
| bob      | 2021-01-29 | 0.0         |

Desired output:

| USERNAME | DATE       | OT_FIRST-5_HRS | OT_AFTER_5_HRS |
|----------|------------|---------------------------------|
| bob      | 2021-01-25 | 2.0            | 0.0            |
| bob      | 2021-01-26 | 2.0            | 0.0            |
| bob      | 2021-01-27 | 1.0            | 2.0            |
| bob      | 2021-01-28 | 0.0            | 2.0            |
| bob      | 2021-01-29 | 0.0            | 0.0            |

TO clarify, OT_FIRST_5_HRS is the first 5 hrs for the entire query period, not for just the day. The same applies to OT_AFTER_5_HRS.

The 1 week period is just an example, but ideally the solution would apply to any range of dates.

Note that I'm working with SQL Server 2016.

Any help would be greatly appreciated.

Answer 1

You can use window functions. For this purpose, a subquery makes the logic easier to follow:

SELECT USERNAME, DATE, OT_DURATION,
       (CASE WHEN RUNNING_OT < 5 THEN OT_DURATION
             WHEN RUNNING_OT - OT_DURATION < 5 THEN 5 - (RUNNING_OT - OT_DURATION)
             ELSE 0
        END) as ot_first,
       (CASE WHEN RUNNING_OT - OT_DURATION >= 5 THEN OT_DURATION
             WHEN RUNNING_OT - OT_DURATION < 5 THEN RUNNING_OT - 5
             ELSE 0
        END) as ot_first
FROM (SELECT USERNAME, DATE,
             SUM(DATEDIFF(SECOND, STARTTIME, ENDTIME) / 3600.0) AS OT_DURATION,
             SUM(SUM(DATEDIFF(SECOND, STARTTIME, ENDTIME) / 3600.0)) OVER (PARTITION BY USERNAME ORDER BY DATE) as RUNNING_OT
      WHERE EVENT_TYPE = 'OVERTIME' AND
            DATE BETWEEN '2021-01-25' AND '2021-01-31'
      FROM EVENTS
     ) e;

Note: Your data is for one week due to the WHERE clause. If you want this for multiple weeks, then you need to include the "week" logic in the PARTITION BY clause. I haven't done so because the definition of week can be finicky and that is not really related to the question you are asking.