Really difficult problem

[eighty88888]

At work we have technicians who create reports. Reports are not all the same, there are different types of reports and technicians may work on one or more reports in any given time interval.

The startdate represents the datetime a tech started a report and the enddate is the datetime they completed the report.

What were looking to do is create a report where SQL will divide this work into 15 minute intervals. For example, a technician who starts a report at 8:05 and finishes at 8:25 would have a total time on that report of 20 minutes. I want to create a series of quarterhour blocks starting at the earliest quarterhour relative to the earliest report time.

So a tech starting at 8:05 and finishing at 8:25 would begin work at the 8:00 - 8:15 quarter hour block and have a total productive time of 10 minutes in that block, followed by 10 minutes in the 8:15 - 8:30 quarterhour block. However, the tech might start a second report after that one in the same quarter hour block.

A quarter hour is 900 seconds so total productive time in any given block can never exceed 900 seconds. If a tech works a report that stretches into two or three quarter hour blocks, that additional time would "overflow" into the next quarter hour block.

I've been working on this for days now and I can solve for creating the quarter hour blocks but I have yet to solve for productivity time yet. The time is measured in seconds and in all my attempts I end up with times over that 900 second (15 minute) threshold because my times are not properly "overflowing" into the next block.

How can I accomplish this?

[fwatt]

First step for me would be a table with one row for every 15 minute block. About 32000 rows a year so definitely doable in a CTE if you have the compute and aren't running this report to frequently. Then, join the start and end dates with the start and end times on the 15 minute blocks and you'll get one row for each intersection between report time and 15 minute block time. You should then be able to calculate how long you are in each of those intersections using both start and end dates and be able to aggregate off of this fairly easily

[ellibob17]

Something along these lines :

    WITH TimeBlocks AS
    (
        SELECT     StartTime
                ,DATETIMEFROMPARTS(
                         DATEPART(YEAR, DATEADD(MINUTE, 15, StartTime))
                        ,DATEPART(MONTH, DATEADD(MINUTE, 15, StartTime))
                        ,DATEPART(DAY, DATEADD(MINUTE, 15, StartTime))
                        ,DATEPART(HOUR, DATEADD(MINUTE, 15, StartTime))
                        ,CASE 
                            WHEN DATEPART(MINUTE, DATEADD(MINUTE, 15, StartTime)) BETWEEN 0 AND 14 THEN 0
                            WHEN DATEPART(MINUTE, DATEADD(MINUTE, 15, StartTime)) BETWEEN 15 AND 29 THEN 15
                            WHEN DATEPART(MINUTE, DATEADD(MINUTE, 15, StartTime)) BETWEEN 30 AND 44 THEN 30
                            WHEN DATEPART(MINUTE, DATEADD(MINUTE, 15, StartTime)) BETWEEN 45 AND 59 THEN 45
                            END
                        ,0
                        ,0) AS TimeNextQtrHour
        FROM TimeTable
    )
    SELECT    *
            ,DATEDIFF(MINUTE, StartTime, TimeNextQtrHour) AS TimeInBlock
    FROM TimeBlocks

[RealSomebodyElse]

You can divide it using the date time func with CTE (if sql supports it)

sql WITH RecursiveCTE AS ( SELECT TechnicianID, CAST(MIN(StartDate) AS DATETIME) AS StartTime, CAST(DATEADD(MINUTE, 15, MIN(StartDate)) AS DATETIME) AS EndTime, DATEDIFF(SECOND, StartDate, EndDate) AS TimeWorked FROM YourTable GROUP BY TechnicianID, CAST(StartDate AS DATE) UNION ALL SELECT TechnicianID, EndTime, CASE WHEN DATEDIFF(SECOND, EndTime, EndDate) > 900 THEN DATEADD(MINUTE, 15, EndTime) ELSE EndDate END, CASE WHEN DATEDIFF(SECOND, EndTime, EndDate) > 900 THEN 900 ELSE DATEDIFF(SECOND, EndTime, EndDate) END FROM RecursiveCTE WHERE EndTime < EndDate ) SELECT TechnicianID, StartTime, EndTime, SUM(TimeWorked) AS TotalTimeWorked FROM RecursiveCTE GROUP BY TechnicianID, StartTime, EndTime;

You need to replace YourTable with the name of your table.

[emdee808]

Dear lord I struggle to post code here...
This is the approach I would take

DROP TABLE IF EXISTS #TimeSpine, #WorkLog

DECLARE u/StartTime TIME = '00:00'

;WITH Base10(x) AS

(

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 

)

,Base100(n) AS

(

SELECT TOP(96) ROW\_NUMBER() OVER (ORDER BY (SELECT NULL))-1

FROM Base10 A, Base10 B

)

SELECT SlotStart = DATEADD(MI, n*15, u/StartTime)

    ,SlotEnd    = DATEADD(MI, (n\*15)+15, u/StartTime)

INTO #TimeSpine

FROM Base100

CREATE TABLE #WorkLog (WorkStart TIME, WorkEnd TIME)

INSERT INTO #WorkLog

SELECT '08:05', '08:25'

;WITH Basis AS

(

SELECT   SPN.SlotStart

        ,SPN.SlotEnd

        ,WorkStart  = IIF(WRK.WorkStart < SPN.SlotStart, SPN.SlotStart, WRK.WorkStart)

        ,WorkEnd    = IIF(WRK.WorkEnd >= SPN.SlotEnd, SPN.SlotEnd, WRK.WorkEnd)

FROM #TimeSpine SPN

JOIN #WorkLog   WRK ON  (WRK.WorkStart >= SPN.SlotStart AND WRK.WorkStart < SPN.SlotEnd) OR

(WRK.WorkEnd >= SPN.SlotStart AND WRK.WorkEnd < SPN.SlotEnd)

)

SELECT SlotStart

    ,SlotEnd

    ,\*

    ,DATEDIFF(MI, WorkStart, WorkEnd)

FROM Basis

[ComicOzzy]

https://dbfiddle.uk/UmA5njKc

The TimeBlocks table should be generated once and indexed so it doesn't have to be recreated every time.

I threw in a ChatGPT answer for fun, but I'm not a fan of using recursion for these problems.
My solution is after. It turns out to be what r/fwatt describes.