Calculate Work Time From Shift And Break Table

I am using SQL Server 2008. I have two table Shift and Break with following data: The shifts will be 7 days week. Data in SHIFT TABLE ID Desc Start_Time End_Time 1 1st 07:20:00

Solution 1:

As others have stated this is a bit vague without assuming a few things. There are faster, less complicated, ways to this issue but I tried to do my solution as dynamic as possible to suit the vague definition. Here are my assumptions:

Here is the SQL Fiddle: SQL Fiddle Demo

Assumptions

• Assuming SQL Server 2005+
• Assuming the shift tables date part is 1900-01-01
• Assumes the Break table has proper date for StartTime / EndTime
• Assumes only 1 person in the database clocking in and out (No EmployeeID included in @BREAK table)
• Assumes the work they entire shift. Clocking in precisely at the shift start and out at the shift end.

Tables

DECLARE@SHIFTTable (ID INTIDENTITY(1,1) PRIMARY KEY, StartTime DATETIME, EndTime DATETIME)
INSERTINTO@SHIFT (StartTime, EndTime) VALUES 
('07:20:00','15:20:00'),
('15:20:00','23:20:00'),
('23:20:00','07:20:00')

DECLARE@BREAKTable (ID INTIDENTITY(1,1) PRIMARY KEY, StartTime DATETIME, EndTime DATETIME)
INSERTINTO@BREAK (StartTime, EndTime) VALUES
('1/1/2013 09:10:00','1/1/2013 09:25:00'),
('1/1/2013 11:30:00','1/1/2013 12:05:00'),
('1/1/2013 13:30:00','1/1/2013 13:45:00'),
('1/1/2013 17:10:00','1/1/2013 17:25:00'),
('1/1/2013 19:30:00','1/1/2013 20:05:00'),
('1/1/2013 21:30:00','1/1/2013 21:45:00'),
('1/2/2013 01:10:00','1/2/2013 01:25:00'),
('1/2/2013 03:30:00','1/2/2013 04:05:00'),
('1/2/2013 05:30:00','1/2/2013 05:45:00'),

('1/2/2013 09:10:00','1/2/2013 09:25:00'),
('1/2/2013 11:30:00','1/2/2013 12:05:00'),
('1/2/2013 13:30:00','1/2/2013 13:45:00'),
('1/2/2013 17:10:00','1/2/2013 17:25:00'),
('1/2/2013 19:30:00','1/2/2013 20:05:00'),
('1/2/2013 21:30:00','1/2/2013 21:45:00'),
('1/2/2013 01:10:00','1/2/2013 01:25:00'),
('1/2/2013 03:30:00','1/2/2013 04:05:00'),
('1/2/2013 05:30:00','1/2/2013 05:45:00')

Solution

;WITH
MinMaxDates AS--FINDS THE MINIMUM AND MAXIMUM DATE RANGES NEEDING SHIFTS ASSOCIATED.
(
    SELECTCAST(MIN(B.StartTime) ASDATE) AS MinDate, 
        CAST(MAX(B.EndTime) ASDATE) AS MaxDate 
    FROM@BREAKAS B
),
RecursiveDateBuilder AS--RECURSIVELY BUILDS A LIST OF DATES BETWEEN THE MINIMUM AND MAXIMUM RANGES IN BREAKS
(
    SELECT MinDate AS ShiftStartDate FROM MinMaxDates
    UNIONALLSELECT DATEADD(dd,1,ShiftStartDate) FROM RecursiveDateBuilder WHERE DATEADD(dd,1,ShiftStartDate) <= (SELECT MaxDate FROM MinMaxDates)
),
ShiftSets AS--CREATE A SHIFT SET FOR EVERY DATE
(
    SELECTROW_NUMBER() OVER (ORDERBY R.ShiftStartDate ASC, S.ID ASC) AS NewShiftID,
        S.ID AS OldShiftID, 
        DATEADD(dd,DATEDIFF(dd,S.StartTime, R.ShiftStartDate),S.StartTime) AS StartDate,
        DATEADD(dd,DATEDIFF(dd,S.EndTime, R.ShiftStartDate),S.EndTime) AS EndDate,
        R.ShiftStartDate AS ShiftGroup
    FROM@SHIFTAS S
        CROSSJOIN RecursiveDateBuilder AS R
),
Shifts AS--FIXES ANY SHIFTS THAT CROSS MIDNIGHT SETTING THEM TO THE NEXT DAY
(
SELECT
    S.NewShiftID AS ShiftID,
    S.StartDate,
    CASEWHEN S.EndDate <= Min2.MinStartDate THEN DATEADD(DAY,1,S.EndDate) 
        ELSE S.EndDate 
    ENDAS EndDate
FROM
    ShiftSets AS S
    CROSS APPLY (SELECTMIN(Mins.StartDate) AS MinStartDate FROM ShiftSets AS Mins WHERE Mins.ShiftGroup = S.ShiftGroup) AS Min2 
),
BreaksToShifts AS--ASSOCIATES THE PUNCHES TO THE SHIFTS
(
    SELECT
        B.StartTime AS ClockIn,
        B.EndTime AS ClockOut,
        S.ShiftID,
        S.StartDate,
        S.EndDate
    FROM@BREAKAS B
        INNERJOIN Shifts AS S ON (B.StartTime BETWEEN S.StartDate AND S.EndDate AND B.EndTime BETWEEN S.StartDate AND S.EndDate)
),
Punches AS
(
    SELECTROW_NUMBER() OVER (ORDERBY S.TheTime ASC) AS ID, S.TheTime FROM 
    (
        SELECT BS.ShiftID, BS.ClockIn AS TheTime FROM BreaksToShifts AS BS
        UNIONALLSELECT BS.ShiftID, MIN(BS.StartDate) AS TheTime FROM BreaksToShifts AS BS GROUPBY BS.ShiftID
        UNIONALLSELECT BS.ShiftID, BS.ClockOut AS TheTime FROM BreaksToShifts AS BS
        UNIONALLSELECT BS.ShiftID, MAX(BS.EndDate) AS TheTime FROM BreaksToShifts AS BS GROUPBY BS.ShiftID
    ) AS S
)
SELECT*FROM
    Punches AS P1
    INNERJOIN Punches AS P2 ON (P2.ID = P1.ID +1)
WHERE
    P1.ID %2>0