Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
235 views
in Technique[技术] by (71.8m points)

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

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.

question from:https://stackoverflow.com/questions/66046355/group-durations-in-buckets-for-entire-query-date-range-then-display-by-date

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...