November 18, 2009 at 6:18 pm
Hello,
First off please let me say TSQL is not my native language and I'm as sure as I can be that there must be a better way to do this. The requirement was to create a function that takes two datetimes (start and end) and calculates the business minutes between them.
The code has to take into account the following:
1. Public holidays.
2. Lunch break starting at 12:00 and finishing at 12:30 each work day.
3. Standard working day starting at 08:30.
4. Standard working day finishing at 17:00.
I needed to be able to run the code at any time of the day or night and it will return the current business minutes difference between the two dates. I've had a crack at it and it works but there must be a better way? I've had a search and I see a lot of code working out business days just not much for business minutes and less with public holidays.
Below is the code to create the public holiday table and populate it (please excuse the New Zealand holidays) then following that is the function, any thoughts / suggestions would be welcome.
CREATE TABLE dbo.public_holidays ( public_holiday_date DATETIME NOT NULL
, public_holiday_description VARCHAR(50) NULL)
ALTER TABLE dbo.public_holidays
ADD CONSTRAINT PK_public_holidays PRIMARY KEY CLUSTERED (public_holiday_date)
INSERT INTO dbo.public_holidays
SELECT '2006-01-01 00:00:00', 'New Years Day' UNION
SELECT '2007-01-01 00:00:00', 'New Years Day' UNION
SELECT '2008-01-01 00:00:00', 'New Years Day' UNION
SELECT '2009-01-01 00:00:00', 'New Years Day' UNION
SELECT '2006-01-02 00:00:00', 'Day after New Years Day' UNION
SELECT '2007-01-02 00:00:00', 'Day after New Years Day' UNION
SELECT '2008-01-02 00:00:00', 'Day after New Years Day' UNION
SELECT '2009-01-02 00:00:00', 'Day after New Years Day' UNION
SELECT '2006-02-06 00:00:00', 'Waitangi Day' UNION
SELECT '2007-02-06 00:00:00', 'Waitangi Day' UNION
SELECT '2008-02-06 00:00:00', 'Waitangi Day' UNION
SELECT '2009-02-06 00:00:00', 'Waitangi Day' UNION
SELECT '2006-04-14 00:00:00', 'Good Friday' UNION
SELECT '2007-04-06 00:00:00', 'Good Friday' UNION
SELECT '2008-03-21 00:00:00', 'Good Friday' UNION
SELECT '2009-04-10 00:00:00', 'Good Friday' UNION
SELECT '2006-04-17 00:00:00', 'Easter Monday' UNION
SELECT '2007-04-09 00:00:00', 'Easter Monday' UNION
SELECT '2008-03-24 00:00:00', 'Easter Monday' UNION
SELECT '2009-04-13 00:00:00', 'Easter Monday' UNION
SELECT '2006-04-25 00:00:00', 'ANZAC Day' UNION
SELECT '2007-04-25 00:00:00', 'ANZAC Day' UNION
SELECT '2008-04-25 00:00:00', 'ANZAC Day' UNION
SELECT '2009-04-25 00:00:00', 'ANZAC Day' UNION
SELECT '2006-06-05 00:00:00', 'Queens Birthday' UNION
SELECT '2007-06-04 00:00:00', 'Queens Birthday' UNION
SELECT '2008-06-02 00:00:00', 'Queens Birthday' UNION
SELECT '2009-06-01 00:00:00', 'Queens Birthday' UNION
SELECT '2006-10-23 00:00:00', 'Labour Day' UNION
SELECT '2007-10-22 00:00:00', 'Labour Day' UNION
SELECT '2008-10-27 00:00:00', 'Labour Day' UNION
SELECT '2009-10-26 00:00:00', 'Labour Day' UNION
SELECT '2006-12-25 00:00:00', 'Christmas Day' UNION
SELECT '2007-12-25 00:00:00', 'Christmas Day' UNION
SELECT '2008-12-25 00:00:00', 'Christmas Day' UNION
SELECT '2009-12-25 00:00:00', 'Christmas Day' UNION
SELECT '2006-12-26 00:00:00', 'Boxing Day' UNION
SELECT '2007-12-26 00:00:00', 'Boxing Day' UNION
SELECT '2008-12-26 00:00:00', 'Boxing Day' UNION
SELECT '2009-12-28 00:00:00', 'Boxing Day' UNION
SELECT '2006-11-17 00:00:00', 'Canterbury Anniversary Day' UNION
SELECT '2007-11-16 00:00:00', 'Canterbury Anniversary Day' UNION
SELECT '2008-11-14 00:00:00', 'Canterbury Anniversary Day' UNION
SELECT '2009-11-13 00:00:00', 'Canterbury Anniversary Day'
CREATE FUNCTION dbo.udf_GetBusinessMinutes ( @StartDate DATETIME
, @EndDate DATETIME)
RETURNS INT AS
BEGIN
-- Declare Variables
DECLARE @BusinessMinutes INT
DECLARE @WorkStartTime VARCHAR(9)
DECLARE @WorkEndTime VARCHAR(9)
DECLARE @LunchStartTime VARCHAR(9)
DECLARE @LunchEndTime VARCHAR(9)
DECLARE @LunchDuration INT
DECLARE @WorkdayDuration INT
DECLARE @WorkStartDateOnly DATETIME
DECLARE @WorkEndDateOnly DATETIME
DECLARE @Check BIT
DECLARE @Counter INT
DECLARE @Original_Counter INT
DECLARE @CurrentWorkStart DATETIME
DECLARE @CurrentLunchStart DATETIME
DECLARE @CurrentLunchEnd DATETIME
DECLARE @CurrentWorkEnd DATETIME
-- Set Variables
SET @BusinessMinutes = 0
SET @WorkStartTime = ' 08:30:00'
SET @LunchStartTime = ' 12:00:00'
SET @LunchEndTime = ' 12:30:00'
SET @WorkEndTime = ' 17:00:00'
SET @LunchDuration = 30
SET @WorkdayDuration = 480
SET @WorkStartDateOnly = CAST(LEFT(@StartDate, 11) AS DATETIME)
SET @WorkEndDateOnly = CAST(LEFT(@EndDate, 11) AS DATETIME)
-- Check what the first day of the week is set to and set the sunday and
-- saturday variables accordingly
DECLARE @Sunday INT
DECLARE @Saturday INT
SET @Saturday = CASE @@DATEFIRST WHEN 1 THEN 6
WHEN 2 THEN 5
WHEN 3 THEN 4
WHEN 4 THEN 3
WHEN 5 THEN 2
WHEN 6 THEN 1
WHEN 7 THEN 7 END
SET @Sunday = CASE @@DATEFIRST WHEN 1 THEN 7
WHEN 2 THEN 6
WHEN 3 THEN 5
WHEN 4 THEN 4
WHEN 5 THEN 3
WHEN 6 THEN 2
WHEN 7 THEN 1 END
-- Make sure the start date is not a weekend or public holiday.
-- If it is then set it to be the start time on the next work day.
SET @Check = 1
WHILE @Check = 1
BEGIN
IF DATEPART(WEEKDAY, @StartDate) IN (@Saturday, @Sunday) OR
@WorkStartDateOnly IN (SELECT public_holiday_date
FROM dbo.public_holidays)
BEGIN
SET @StartDate = CAST(LEFT(DATEADD(DAY, 1, @StartDate), 11) + @WorkStartTime AS DATETIME)
SET @WorkStartDateOnly = CAST(LEFT(@StartDate, 11) AS DATETIME)
END
ELSE
BEGIN
SET @Check = 0
END
END
-- Make sure the end date is not a weekend or public holiday.
-- If it is then set it to be the start time on the next work day.
SET @Check = 1
WHILE @Check = 1
BEGIN
IF DATEPART(WEEKDAY, @EndDate) IN (@Saturday, @Sunday) OR
@WorkEndDateOnly IN (SELECT public_holiday_date
FROM dbo.public_holidays)
BEGIN
SET @EndDate = CAST(LEFT(DATEADD(DAY, 1, @EndDate), 11) + @WorkStartTime AS DATETIME)
SET @WorkEndDateOnly = CAST(LEFT(@EndDate, 11) AS DATETIME)
END
ELSE
BEGIN
SET @Check = 0
END
END
-- Set a counter to be the difference in days between the start date and end date. Loop through
-- each day adding the appropriate business minutes.
SET @Counter = DATEDIFF(DAY, @WorkStartDateOnly, @WorkEndDateOnly)
SET @Original_Counter = DATEDIFF(DAY, @WorkStartDateOnly, @WorkEndDateOnly)
WHILE @Counter >= 0
BEGIN
SET @CurrentWorkStart = CAST(LEFT(@StartDate, 11) + @WorkStartTime AS DATETIME)
SET @CurrentLunchStart = CAST(LEFT(@StartDate, 11) + @LunchStartTime AS DATETIME)
SET @CurrentLunchEnd = CAST(LEFT(@StartDate, 11) + @LunchEndTime AS DATETIME)
SET @CurrentWorkEnd = CAST(LEFT(@StartDate, 11) + @WorkEndTime AS DATETIME)
-- Check and if we are processing the first or last day then calculate the business minutes
-- taking into account the standard working hours. We already know that the start and end
-- date will not be a weekend or public holiday as we set them in the last area.
IF @Counter = @Original_Counter OR
@Counter = 0
BEGIN
-- 1 Start time less than work start time and end date greater than or equal to work end time.
IF @StartDate < @CurrentWorkStart AND
@EndDate >= @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + @WorkdayDuration
END
-- 2 Start time less than work start time and end time greater than or equal to lunch end time and
-- end time less than work end time.
IF @StartDate < @CurrentWorkStart AND
@EndDate >= @CurrentLunchEnd AND
@EndDate < @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @CurrentWorkStart, @EndDate) - @LunchDuration
END
-- 3 Start time less than work start time and end time greater than or equal to lunch start time and
-- end time less than lunch end time.
IF @StartDate < @CurrentWorkStart AND
@EndDate >= @CurrentLunchStart AND
@EndDate < @CurrentLunchEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @CurrentWorkStart, @CurrentLunchStart)
END
-- 4 Start time less than work start time and end time greater than or equal to work start time and
-- end time less than lunch start time.
IF @StartDate < @CurrentWorkStart AND
@EndDate >= @CurrentWorkStart AND
@EndDate < @CurrentLunchStart
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @CurrentWorkStart, @EndDate)
END
-- 5 Start time greater than or equal to work start time and start time less than lunch start time and
-- end time greater than or equal to work end time
IF @StartDate >= @CurrentWorkStart AND
@StartDate < @CurrentLunchStart AND
@EndDate >= @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @CurrentWorkEnd) - @LunchDuration
END
-- 6 Start time greater than or equal to lunch start time and start time less than lunch end time and
-- end time greater than or equal to work end time
IF @StartDate >= @CurrentLunchStart AND
@StartDate < @CurrentLunchEnd AND
@EndDate >= @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @CurrentLunchEnd, @CurrentWorkEnd)
END
-- 7 Start time greater than or equal to lunch end time and start time less than work end time and
-- end time greater than or equal to work end time
IF @StartDate >= @CurrentLunchEnd AND
@StartDate < @CurrentWorkEnd AND
@EndDate >= @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @CurrentWorkEnd)
END
-- 8 Start time greater than or equal to work start time and start time less than lunch start time and
-- end time greater than or equal to lunch end time and end time less than work end time
IF @StartDate >= @CurrentWorkStart AND
@StartDate < @CurrentLunchStart AND
@EndDate >= @CurrentLunchEnd AND
@EndDate < @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @EndDate) - @LunchDuration
END
-- 9 Start time greater than or equal to work start time and start time less than lunch start time and
-- end time greater than or equal to lunch start time and end time less than lunch end time
IF @StartDate >= @CurrentWorkStart AND
@StartDate < @CurrentLunchStart AND
@EndDate >= @CurrentLunchStart AND
@EndDate < @CurrentLunchEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @CurrentLunchStart)
END
-- 10 Start time greater than or equal to work start time and start time less than lunch start time and
-- end time greater than or equal to work start time and end time less than lunch start time
IF @StartDate >= @CurrentWorkStart AND
@StartDate < @CurrentLunchStart AND
@EndDate >= @CurrentWorkStart AND
@EndDate < @CurrentLunchStart
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @EndDate)
END
-- 11 Start time greater than or equal to lunch start time and start time less than lunch end time and
-- end time greater than or equal to lunch start time and end time less than lunch end time
IF @StartDate >= @CurrentLunchStart AND
@StartDate < @CurrentLunchEnd AND
@EndDate >= @CurrentLunchStart AND
@EndDate < @CurrentLunchEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + 0
END
-- 12 Start time greater than or equal to lunch end time and start time less than work end time and
-- end time greater than or equal to lunch end time and end time less than work end time
IF @StartDate >= @CurrentLunchEnd AND
@StartDate < @CurrentWorkEnd AND
@EndDate >= @CurrentLunchEnd AND
@EndDate < @CurrentWorkEnd
BEGIN
SET @BusinessMinutes = @BusinessMinutes + DATEDIFF(MINUTE, @StartDate, @EndDate)
END
END
ELSE
-- If the date is not the first or last day then just add the standard work day duration
-- as long as the day is not a weekend or public holiday.
BEGIN
IF DATEPART(WEEKDAY, @StartDate) NOT IN (@Saturday, @Sunday) AND
CAST(LEFT(@CurrentWorkStart, 11) AS DATETIME) NOT IN (SELECT public_holiday_date
FROM dbo.public_holidays)
BEGIN
SET @BusinessMinutes = @BusinessMinutes + @WorkdayDuration
END
END
SET @StartDate = CAST(LEFT(DATEADD(DAY, 1, @StartDate), 11) AS DATETIME)
SET @Counter = @Counter - 1
END
RETURN @BusinessMinutes
END
Kind regards,
Chris
November 18, 2009 at 7:45 pm
Here's how I would approach it. Note that I didn't do the math on the numbers CTE, I could have overblown the number I needed, but it's a one time setup of the table, so a bit of waste isn't that big a deal. The table creates in about 3 minutes on my dev box.
CREATE TABLE Calendar(
CalDatesmalldatetime,
IsWorkingbit DEFAULT 1
)
CREATE CLUSTERED INDEX CX_CalDate ON Calendar(CalDate, IsWorking)
GO
DECLARE @SD datetime
SET @SD = '1/1/2006' -- Set this to whatever you need
-- Numbers Code courtesy of Itzik Ben-Gan
;WITH Nbrs_6( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_5( n ) AS ( SELECT 1 FROM Nbrs_6 n1 CROSS JOIN Nbrs_6 n2 ),
Nbrs_4( n ) AS ( SELECT 1 FROM Nbrs_5 n1 CROSS JOIN Nbrs_5 n2 ),
Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
INSERT INTO Calendar(CalDate)
SELECT DATEADD(n,n,@SD)
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 4733280; --This is the number of minutes between '1/1/2006' and '1/1/2015'
GO
-- Now update the IsWorking column for things like holidays, weekends, lunchtime, etc.
--Function looks a bit simpler:
CREATE FUNCTION SCA_WorkMinutes(
@Startdatetime,
@Enddatetime)
RETURNS int
AS
BEGIN
RETURN( SELECT COUNT(*) FROM Calendar
WHERE IsWorking = 1 AND CalDate >=@Start AND CalDate < @End)
END
GO
SELECT dbo.SCA_WorkMinutes('11/16/2008',GETDATE())
November 18, 2009 at 7:53 pm
Also, the table gives you a couple other benefits:
If you don't have any rhyme or reason for a time that is not work time (The building catches on fire and you close down early), you just update a few rows, rather than have to remember to go back and mod the function and add more code to handle that specific day.
You can add additional bit fields to easily extend what it can report on. Need to know how many holiday minutes there were within that period? Just add another bit field.
November 23, 2009 at 8:32 pm
Hi there,
Thanks for the reply, I appreciate your time. It looks pretty good, I'm setting it up now but think that this is something that I'd quite happily use. Much simpler!
Kind regards,
Chris
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply