FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- Step 1: Declare and populate a table variable with sample labor tickets
DECLARE @LaborTickets TABLE (
    TicketID INT IDENTITY(1,1),
    StartTime DATETIME,
    EndTime DATETIME
);

INSERT INTO @LaborTickets (StartTime, EndTime)
VALUES
    ('2024-02-04 06:00:00', '2024-02-04 07:00:00'),
    ('2024-02-04 05:30:00', '2024-02-04 07:30:00'),
    ('2024-02-04 05:00:00', '2024-02-04 07:00:00');

-- Step 2: Calculate overlapping time segments
WITH TimeSegments AS (
    SELECT DISTINCT StartTime AS TimePoint FROM @LaborTickets
    UNION
    SELECT DISTINCT EndTime FROM @LaborTickets
),
RankedSegments AS (
    SELECT TimePoint, 
           LEAD(TimePoint) OVER (ORDER BY TimePoint) AS NextTimePoint
    FROM TimeSegments
),
SegmentOverlaps AS (
    SELECT r.TimePoint AS StartTime, 
           r.NextTimePoint AS EndTime,
           COUNT(*) AS OverlapCount
    FROM RankedSegments r
    JOIN @LaborTickets t ON r.TimePoint >= t.StartTime AND r.NextTimePoint <= t.EndTime
    WHERE r.NextTimePoint IS NOT NULL
    GROUP BY r.TimePoint, r.NextTimePoint
),
TicketHours AS (
    SELECT t.TicketID, t.StartTime, t.EndTime,
           SUM(
               CASE 
                   WHEN s.StartTime >= t.StartTime AND s.EndTime <= t.EndTime 
                   THEN DATEDIFF(MINUTE, s.StartTime, s.EndTime) / 60.0 / s.OverlapCount 
                   ELSE 0 
               END
           ) AS Hours
    FROM @LaborTickets t
    LEFT JOIN SegmentOverlaps s ON s.StartTime >= t.StartTime AND s.EndTime <= t.EndTime
    GROUP BY t.TicketID, t.StartTime, t.EndTime
)
SELECT StartTime, EndTime, Hours
FROM TicketHours
ORDER BY StartTime;

StartTime	EndTime	Hours
2024-02-04 05:00:00.000	2024-02-04 07:00:00.000	1.08333333333333333
2024-02-04 05:30:00.000	2024-02-04 07:30:00.000	1.08333333333333333
2024-02-04 06:00:00.000	2024-02-04 07:00:00.000	.33333333333333333

(3 rows affected)

(3 rows affected)