Summarize start and end durations by 30 minute interval

Question

0.00/5 (No votes)

See more:

I have a SQL query that provides scheduled activity start and end times grouped by date and employee. The duration of these can span in excess of 30 minutes. I am looking to be able to show duration based on 30 minute intervals. For example, if 1 record shows a start time of 10:00 am and end time of 11:15 am (75 minutes), I want to be able to see in 30 minute intervals how much time was spent in that activity, so there would be 3 records with the 10am and 10:30am intervals showing 30 mintutes in duration and the 11 am interval showing 15 minutes in duration.

Here is an example of the SQL I am starting with.

Select 
 Date,
 FIRSTNAME,
 LASTNAME,
 ID,
 ACTIVITY,
 START_EST,
 END_EST,
 DATEDIFF(minute, START_EST, END_EST) as DURATION
From #TMPSHRINK

What I have tried:

I have tried various case statements with nothing aggregating correctly.

Posted 26-Apr-24 10:41am

Dbarton0231

Updated 26-Apr-24 11:02am

Add a Solution

Comments

CHill60 29-Apr-24 5:55am

You will get better responses if you share some sample data and the expected results from that data

Dbarton0231 29-Apr-24 7:02am

The top is an example of what I have and the bottom is an example of what I am looking for:

Date Name ID Activity Start Time End Tine Duration
4/28/2024 John Doe 123 Meeting 10:15 AM 11:30 AM 75

Date Name ID Activity Start Interval End Interval Duration
4/28/2024 John Doe 123 Meeting 10:00 AM 10:30 AM 15
4/28/2024 John Doe 123 Meeting 10:30 AM 11:00 AM 30
4/28/2024 John Doe 123 Meeting 11:00 AM 11:30 AM 30

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-04-30T02:01:00

Assuming Microsoft SQL Server 2022, something like this should work:

SQL

WITH cteTimeSlots (SlotStart, SlotEnd) As
(
    SELECT
        Convert(time, '00:00'),
        Convert(time, '00:30')
    
    UNION ALL
    
    SELECT
        DateAdd(minute, 30, SlotStart),
        DateAdd(minute, 30, SlotEnd)
    FROM
        cteTimeSlots
    WHERE
        SlotStart < '23:30'
)
SELECT
    S.Date,
    S.FIRSTNAME,
    S.LASTNAME,
    S.ID,
    S.ACTIVITY,
    T.SlotStart,
    T.SlotEnd,
    DateDiff(minute, GREATEST(T.SlotStart, S.START_EST), LEAST(T.SlotEnd, S.END_EST)) As Duration
FROM
    #TMPSHRINK As S
    INNER JOIN cteTimeSlots As T
    ON T.SlotEnd > S.START_EST
    AND T.SlotStart < S.END_EST
;

For earlier versions of SQL Server, replace GREATEST / LEAST with a CASE statement:

SQL

DateDiff(minute, CASE WHEN T.SlotStart > S.START_EST THEN T.SlotStart ELSE S.START_EST END, CASE WHEN T.SlotEnd < S.END_EST THEN T.SlotEnd ELSE S.END_EST END) As Duration