Saturday, February 15, 2014

Calculate Working Hours Between Two Dates Excluding Lunch Break andHolidays

I got the ideas of the SQL stored procedures (SPs) for calculating working hours between two dates originally from someone's blog. I tried to re-search the blog to mention the link here, but to no avail. Anyway, thank you very much to the owner. If you read this please do let me know. 
Nevertheless, the SPs don't meet my exact requirement. Beside considering holidays I also need to consider the lunch break. So I modified the original stored procedures a bit. 


Firstly, create SP to create holiday tables. 
CREATE TABLE [dbo].[Holiday]( [Holiday] [datetime] NULL)

INSERT INTO Holiday VALUES ('2014-01-14')
-- you may add all holidays afterwards
Secondly, create SP to define the business hours and lunch break.
ALTER FUNCTION [dbo].[fn_CalendarTable]
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS
    @Return TABLE (
        [Date]         DATETIME     NULL,
        [Day]          INT          NULL,
        [WorkingHours] FLOAT        NULL,
        [BeginTime]    VARCHAR (50) NULL,
        [EndTime]      VARCHAR (50) NULL,
        [BeginBreak]   VARCHAR (50) NULL,
        [EndBreak]       VARCHAR (50) NULL)
AS
BEGIN
       DECLARE @CurrentDate datetime
 
       SET @CurrentDate = CONVERT( varchar, @StartDate , 101)
 
       WHILE(@CurrentDate < @EndDate)
       BEGIN
 
              DECLARE @WorkingHours float
              DECLARE @Day int
              DECLARE @BeginTime varchar(50)
              DECLARE @EndTime varchar(50)
              DECLARE @BeginBreak varchar(50)
              DECLARE @EndBreak varchar(50)
 
              SET @Day = datepart(dw, @CurrentDate)
 
              IF @Day = 1 OR @Day = 7
                     BEGIN
                           SET @WorkingHours = 0
                           SET @BeginTime = ''
                           SET @EndTime = ''
                           SET @BeginBreak = ''
                           SET @EndBreak = ''
                     END
              ELSE
                     BEGIN
 
                           SET @WorkingHours = 8
                           SET @BeginTime = '8:30:00 AM'
                           SET @EndTime = '5:30:00 PM'
                           SET @BeginBreak = '12:30:00 PM'
                           SET @EndBreak = '1:30:00 PM'
                     END
 
              INSERT INTO @Return ([date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak)
              VALUES (@CurrentDate, @day, @WorkingHours, @BeginTime, @EndTime, @BeginBreak, @EndBreak)
 
              SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
       END
 
       RETURN
END

Third, create SP to span the dates and get the worked hours.
ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS
RETURN(
       SELECT [date], [day], WorkingHours, BeginTime, EndTime, BeginBreak, EndBreak,
       CASE WHEN h.holiday IS NULL THEN 0 ELSE 1 END Holiday,
       CASE
--            is a bank holiday so nothing will be worked for this day
              WHEN h.holiday IS NOT NULL THEN
                     0

--            started after business hours so ignore this day
              WHEN @StartDate > date + EndTime THEN
                     0

--            ended before business hours on last day so ignore this day
              WHEN @EndDate > date and @EndDate < date + BeginTime THEN
                     0

--            started and ended on the same day within business hours
              WHEN @StartDate > date + BeginTime AND @EndDate < date + EndTime THEN
                CASE  
            --    started/ended before break time or started/ended after break time 
                 WHEN (@StartDate <= date + BeginBreak AND @EndDate <= date + BeginBreak) 
                  OR  (@StartDate >= date + EndBreak AND @EndDate >= date + EndBreak) THEN 
                    DATEDIFF(second, @StartDate, @EndDate) / 60.0  / 60.0 
            --    started/ended within break time 
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak  
                  AND @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN 
                    0 
            --    started within break time 
                 WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak  
                  AND @EndDate >= date + EndBreak THEN 
                    DATEDIFF(second, date + EndBreak, @EndDate) / 60.0  / 60.0 
                 WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak 
                  AND @StartDate <= date + BeginBreak THEN 
                    DATEDIFF(second, @StartDate, date + BeginBreak) / 60.0  / 60.0 
                 ELSE 
                    DATEDIFF(second, @StartDate, @EndDate) / 60.0  / 60.0 - 1 
                END 

--            this is the first day so this will be less than a normal working day
              WHEN @StartDate > date + BeginTime THEN
                CASE
                    WHEN @StartDate >= date + BeginBreak AND @StartDate <= date + EndBreak THEN
                        DATEDIFF(second, date + EndBreak, date + EndTime) / 60.0 / 60.0
                    WHEN @StartDate <= date + BeginBreak THEN
                        DATEDIFF(second, @StartDate, date + EndTime) / 60.0 / 60.0 - 1
                    ELSE
                        DATEDIFF(second, @StartDate, date + EndTime) / 60.0 / 60.0
                END

--            this is the last day so this will be less than a normal working day
              WHEN @EndDate < date + EndTime THEN
                CASE
                    WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak THEN
                        DATEDIFF(second, date + BeginTime, date + BeginBreak) / 60.0 / 60.0
                    WHEN @EndDate >= date + EndBreak THEN
                        DATEDIFF(second, date + BeginTime, @EndDate) / 60.0 / 60.0 - 1
                    ELSE
                        DATEDIFF(second, date + BeginTime, @EndDate) / 60.0 / 60.0
                END

              ELSE
                    WorkingHours
       END WorkedHours
        , @StartDate StartDate, @EndDate EndDate
       from dbo.fn_CalendarTable(@StartDate, @EndDate) cal
       left outer join Holiday h on cal.[date] = h.holiday
       )
GO

/*
SELECT * FROM dbo.fn_BusinessHoursInTimeSpan('2010-10-06 09:00', '2011-03-29 13:30')
*/

If you need to sum, then create SP to sum it.
ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSum
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @Hours FLOAT
    SELECT @Hours = ROUND(SUM(WorkedHours),2) from dbo.[fn_BusinessHoursInTimeSpan](
        @StartDate, @EndDate)
    RETURN @Hours
END

/*
SELECT dbo.fn_BusinessHoursInTimeSpanSum('2010-10-06 08:45', '2010-10-06 19:30') AS TotalHoursWorked
*/
Voila, enter the start date, end date and you'll get SQL calculating the business hours for you. 

When it comes to evaluating SLA or KPI this SPs are very useful.
Post a Comment