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

  • In the script I have defined a Table variable where to store the holidays of the country in question. In the production environment you should use a classic table. An interesting web site that lists the holidays by country is https://en.wikipedia.org/wiki/List_of_holidays_by_country

    In the script I have used Italian Holidays of 2016

  • In the script I have used 3 Tally tables defined as recursive CTEs. You could easily substitute them using a classic Tally table
  • At the end of the script I have left a commented SELECT because it gives details of the calculation and it could be useful to check or debug
  • In my opinion it is interesting how @@DATEFIRST variable and % (Modulo) operator can help to know if a certain day is a weekend day
  • If the Date / Time of the first and last day or if the Times that define the shift are not congruent, the script returns zero
  • If you want to consider one day as 24 hours of work, you can set the shift from “00:00AM” to “11:59PM” (or “00:00” and “23:59”)
  • This script does not consider any break within the shift. If you want, it is not difficult to add two parameters more and slightly modify the queries
---------------
--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)

Rate

5 (2)

Share

Share

Rate

5 (2)