Work time calculation
Some time ago a colleague asked me for a T-SQL script to solve a business problem of calculation. Afterwards the script could have been changed into a TVF, UDF (possibly not) or Stored Procedure according to need.
The problem was the following: we have the date and time of the beginning of a job and the date and time of the end of it. We know the shift time of the worker and a parameter that indicates if we have to consider the weekends and holidays in the calculation.
The script must calculate how many minutes a person has worked in total.
The basic idea of the algorithm is to build a calendar in minutes for the first and last day and, for each minute, to indicate if it must be considered in the computation or not. For all the other days in the middle it is not necessary to calculate every single minute and the calculation is easier.
Notes
---------------
--Holiday Table
---------------
DECLARE @Holidays TABLE (
HolidayDate DATE NOT NULL Primary Key,
HolidayDescription VARCHAR(100) NULL)
--Italian holidays 2016
INSERT INTO @Holidays (HolidayDate, HolidayDescription)
VALUES
('20160101', 'New Year''s Day'),
('20160106', 'Epiphany'),
('20160328', 'Easter Monday'),
('20160425', 'Liberation Day'),
('20160501', 'Internaional Workers'' Day'),
('20160602', 'Republic Day'),
('20160815', 'Assumption Day'),
('20161101', 'All Saints''Day'),
('20161208', 'Immaculate Conception'),
('20161225', 'Christmas Day'),
('20161226', 'St. Stephen''s Day')
------------
--Parameters
------------
DECLARE
@StartDateTime SMALLDATETIME = '20161206 10:00',
@EndDateTime SMALLDATETIME = '20161212 15:59',
@SkipNonWorkingDays BIT = 1,
@StartShift TIME(0) = '09:00',
@EndShift TIME(0) = '16:59'
--------------------
--Internal variables
--------------------
DECLARE
@StartDateTimeIsWeekendDay BIT = 0,
@EndDateTimeIsWeekendDay BIT = 0
-----------------------------
--Settings before starting...
-----------------------------
--The following are necessary to know if @StartDateTime and @EndDateTimeIsWeekendDay are Saturday or Sunday.
--I prefer to calculate them here to simplify the CTEs TabDateStart and TabDateEnd below.
IF ((DATEPART(weekday, @StartDateTime) + @@DATEFIRST) % 7) IN (0, 1)
SET @StartDateTimeIsWeekendDay = 1
IF ((DATEPART(weekday, @EndDateTime) + @@DATEFIRST) % 7) IN (0, 1)
SET @EndDateTimeIsWeekendDay = 1
--The main query starts from here
;WITH
TallyStart (n) AS
(SELECT 0
UNION ALL
SELECT n + 1
FROM TallyStart
WHERE n < DATEDIFF(mi, @StartDateTime,
CASE WHEN DATEDIFF(d, @StartDateTime, @EndDateTime) > 0
THEN DATEADD(MINUTE, -1, DATEADD(DAY, 1, CAST(CAST(@StartDateTime AS DATE) AS DATETIME)))
ELSE @EndDateTime
END))
,TallyBetween (n) AS
(SELECT 1
UNION ALL
SELECT n + 1
FROM TallyBetween
WHERE n < DATEDIFF(d, @StartDateTime, @EndDateTime) - 1)
,TallyEnd (n) AS
(SELECT 0
UNION ALL
SELECT n + 1
FROM TallyEnd
WHERE n < DATEDIFF(mi, CAST(@EndDateTime AS date),
CASE WHEN DATEDIFF(d, @StartDateTime, @EndDateTime) > 0 THEN @EndDateTime
ELSE CAST(@EndDateTime AS date)
END))
,TabDateStart AS
(SELECT DATEADD(MINUTE, n, @StartDateTime) RunningDate,
CASE WHEN (@StartDateTimeIsWeekendDay = 1 AND @SkipNonWorkingDays = 1) OR
(CAST(DATEADD(MINUTE, n, @StartDateTime) AS time(0)) NOT BETWEEN @StartShift AND @EndShift) OR
(holiday.HolidayDate IS NOT NULL AND @SkipNonWorkingDays = 1) THEN 0
ELSE 1 END AS Increment
FROM TallyStart
LEFT JOIN @Holidays holiday ON holiday.HolidayDate = CAST(DATEADD(MINUTE, n, @StartDateTime) AS DATE)
WHERE DATEDIFF(MINUTE, @StartDateTime, @EndDateTime) > 0
)
,TabBetween AS (
SELECT DATEADD(DAY, n, @StartDateTime) RunningDate,
CASE WHEN (((DATEPART(dw, DATEADD(DAY, n, @StartDateTime)) + @@DATEFIRST) % 7) IN (0, 1) AND @SkipNonWorkingDays = 1) OR
(holiday.HolidayDate IS NOT NULL AND @SkipNonWorkingDays = 1) THEN 0
ELSE 1 END AS Increment
FROM TallyBetween
LEFT JOIN @Holidays holiday ON holiday.HolidayDate = CAST(DATEADD(DAY, n, @StartDateTime) AS DATE)
WHERE @StartShift < @EndShift AND
DATEDIFF(DAY, @StartDateTime, DATEADD(DAY, n, @StartDateTime)) > 0 AND
DATEDIFF(DAY, DATEADD(DAY, n, @StartDateTime), @EndDateTime) > 0)
,TabDateEnd AS
(SELECT DATEADD(MINUTE, n, CONVERT(VARCHAR, @EndDateTime, 112)) RunningDate,
CASE WHEN (@EndDateTimeIsWeekendDay = 1 AND @SkipNonWorkingDays = 1) OR
(CAST(DATEADD(MINUTE, n, CONVERT(VARCHAR, @EndDateTime, 112)) AS time(0)) NOT BETWEEN @StartShift AND @EndShift) OR
(holiday.HolidayDate IS NOT NULL AND @SkipNonWorkingDays = 1) THEN 0
ELSE 1 END AS Increment
FROM TallyEnd
LEFT JOIN @Holidays holiday ON DATEDIFF(d, holiday.HolidayDate, @EndDateTime) = 0
WHERE DATEDIFF(DAY, @StartDateTime, @EndDateTime) > 0
)
,DataUnion (Origin, Minutes) AS (
SELECT '1. FirstDay', ISNULL(SUM(Increment), 0) AS [Minutes]
FROM TabDateStart
UNION
SELECT '2. OtherDays',
ISNULL(SUM(DATEDIFF(MINUTE, @StartShift, @EndShift) + 1), 0) AS [Minutes]
FROM TabBetween
WHERE Increment = 1
UNION
SELECT '3. LastDay', ISNULL(SUM(Increment), 0) AS [Minutes]
FROM TabDateEnd)
SELECT SUM(Minutes) as [Minutes]
--Uncomment the line below and comment the line above to look at the details
--SELECT Origin, Minutes
FROM DataUnion
OPTION (MAXRECURSION 0) 
