May 31, 2015 at 2:23 pm
Sorry but this is a complicated question.
I have a table that stores working hrs, such as
RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000
So working times can go over midnight, there can be more than one working period in a day etc.
For this staff member the summary of the weeks work will be
18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000
A Leave of Absence on the 20th, shown in the LEAVE table as
StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000
And was off sick on the morning of the 19th, shown in the SICKNESS Table as
StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000
Now the Summary table should now show
18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times.
Any suggestions how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
Or does anybody know of any good articles on performing this type of date/time calculation.
Many thanks in advance.
June 1, 2015 at 8:43 am
-- Sample data setup
drop TABLE #StaffHours
CREATE TABLE #StaffHours (RecID INT, StaffID INT, StartDate DATETIME, EndDate DATETIME)
INSERT INTO #StaffHours (RecID, StaffID, StartDate, EndDate) VALUES
(17,969,'2015-05-18 00:00:00.000','2015-05-18 06:00:00.000'),
(18,969,'2015-05-18 18:00:00.000','2015-05-19 06:00:00.000'),
(19,969,'2015-05-19 18:00:00.000','2015-05-20 06:00:00.000'),
(20,969,'2015-05-20 18:00:00.000','2015-05-21 06:00:00.000'),
(21,969,'2015-05-21 18:00:00.000','2015-05-22 06:00:00.000'),
(22,969,'2015-05-22 18:00:00.000','2015-05-23 06:00:00.000'),
(23,969,'2015-05-23 14:00:00.000','2015-05-24 08:00:00.000'),
(24,969,'2015-05-24 22:00:00.000','2015-05-25 00:00:00.000')
-- If a shift crosses midnight, then split it into two rows, one for each day:
DROP TABLE #StaffHoursByDay
SELECT RecID, StaffID, StartDate, EndDate,
z.NewStartDate, z.NewEndDate
INTO #StaffHoursByDay
FROM #StaffHours
CROSS APPLY (SELECT [Days] = 1+DATEDIFF(DAY,StartDate,EndDate)) x
CROSS APPLY (SELECT TOP(x.[Days]) n FROM (VALUES(1),(2)) d (n)) y
CROSS APPLY (SELECT
NewStartDate = CASE
WHEN x.[Days] = 1 THEN StartDate
WHEN y.n = 1 THEN StartDate
WHEN y.n = 2 THEN CAST(EndDate AS DATE)
END,
NewEndDate = CASE
WHEN x.[Days] = 1 THEN EndDate
WHEN y.n = 1 THEN CAST(EndDate AS DATE)
WHEN y.n = 2 THEN EndDate
END
) z
WHERE z.NewStartDate <> z.NewEndDate
-- left join to each of the absence tables / one table with each absence type
-- this is just a quick eyeball of what's happening
SELECT
s.RecID, s.StaffID,
s.NewStartDate,
s.NewEndDate,
HoursWorked = CASE WHEN Absence.StaffID IS NULL THEN DATEDIFF(hour,s.NewStartDate,s.NewEndDate) ELSE 0 END,
Absence.*
FROM #StaffHoursByDay s
LEFT JOIN (
VALUES
('Holiday', 969, CAST('2015-05-22 00:00:00.000' AS DATETIME), CAST('2015-05-22 23:59:59.000' AS DATETIME)),
('Leave', 969, CAST('2015-05-20 12:00:00.000' AS DATETIME), CAST('2015-05-20 16:00:00.000' AS DATETIME)),
('Sickness', 969, CAST('2015-05-19 00:00:00.000' AS DATETIME), CAST('2015-05-19 11:59:59.000' AS DATETIME))
) Absence (Absence, StaffID, DateFrom, DateTo)
ON Absence.StaffID = s.StaffID
AND Absence.DateFrom < s.NewEndDate
AND Absence.DateTo > s.NewStartDate
-- Aggregate to extract results
SELECT
s.StaffID,
StartDate = MIN(s.NewStartDate),
EndDate = MAX(s.NewEndDate),
HoursWorked = SUM(CASE WHEN Absence.StaffID IS NULL THEN DATEDIFF(hour,s.NewStartDate,s.NewEndDate) ELSE 0 END)
FROM #StaffHoursByDay s
LEFT JOIN (
VALUES
('Holiday', 969, CAST('2015-05-22 00:00:00.000' AS DATETIME), CAST('2015-05-22 23:59:59.000' AS DATETIME)),
('Leave', 969, CAST('2015-05-20 12:00:00.000' AS DATETIME), CAST('2015-05-20 16:00:00.000' AS DATETIME)),
('Sickness', 969, CAST('2015-05-19 00:00:00.000' AS DATETIME), CAST('2015-05-19 11:59:59.000' AS DATETIME))
) Absence (Absence, StaffID, DateFrom, DateTo)
ON Absence.StaffID = s.StaffID
AND Absence.DateFrom < s.NewEndDate
AND Absence.DateTo > s.NewStartDate
GROUP BY s.StaffID, CAST(s.NewStartDate AS DATE)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply