Click here to Skip to main content
15,946,158 members
Please Sign up or sign in to vote.
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
Updated 26-Apr-24 11:02am
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

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
 
Share this answer
 
Comments
Dbarton0231 30-Apr-24 8:30am    
Brilliant! That works exactly how I had hoped. Thank for the assist!
[no name] 2-Jul-24 6:33am    
@ basketball stars What a marvel! Everything works exactly as I had envisioned it would. I am grateful for your assistance.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900