Calculate Working Hours Exclude Holiday and Various Breaks

Previously I posted about how to calculate working hours between two dates by excluding holidays and lunch break in SQL. It turns out that the requirement is expanded that I also need to consider if there are some days that have not lunch break. I also need to consider different office hours for different countries/companies.

I again modified the SQL and here are they ...

Create holiday table:

CREATE TABLE [dbo].[Holiday](    [CalID] varchar(2),  
                                [Holiday] [smalldatetime] NULL)
GO
INSERT INTO Holiday VALUES ('ID', '2014-01-01')

Here where you define the calendar. If there are many different calendars, you might want to create table for them?

ALTER FUNCTION [dbo].[fn_CalendarTable]
(@CalID VARCHAR(2), @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 @CalID = 'ID'
              BEGIN
                  IF @Day = 1 --Sunday
                         BEGIN
                               SET @WorkingHours = 0
                               SET @BeginTime = ''
                               SET @EndTime = ''
                               SET @BeginBreak = ''
                               SET @EndBreak = ''
                         END
                  ELSE IF @Day = 6 OR @Day = 7 --Friday and Saturday
                         BEGIN
                                 SET @WorkingHours = 4.5
                               SET @BeginTime = '7:30:00 AM'
                               SET @EndTime = '12:00:00 PM'
                               SET @BeginBreak = '12:00:00 PM'
                               SET @EndBreak = '12:00:00 PM'
                         END
                  ELSE --Monday - Thursday
                         BEGIN
                                 SET @WorkingHours = 7.5
                               SET @BeginTime = '7:30:00 AM'
                               SET @EndTime = '3:30:00 PM'
                               SET @BeginBreak = '12:30:00 PM'
                               SET @EndBreak = '1:00:00 PM'
                         END
               END
               ELSE --WHEN 'IN'
               BEGIN
                  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
                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

/*
SELECT * FROM dbo.fn_CalendarTable('ID', '2014-02-08 08:30', '2014-02-14 10:30')
*/

Here the main part, calculate working hours:

ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]
(@CalID VARCHAR(2), @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(minute, @StartDate, @EndDate) / 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(minute, date + EndBreak, @EndDate) / 60.0
                 WHEN @EndDate >= date + BeginBreak AND @EndDate <= date + EndBreak
                  AND @StartDate <= date + BeginBreak THEN
                    DATEDIFF(minute, @StartDate, date + BeginBreak) / 60.0
                 ELSE
                    DATEDIFF(minute, @StartDate, @EndDate) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                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(minute, date + EndBreak, date + EndTime) / 60.0
                    WHEN @StartDate <= date + BeginBreak THEN
                        DATEDIFF(minute, @StartDate, date + EndTime) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                    ELSE
                        DATEDIFF(minute, @StartDate, date + EndTime) / 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(minute, date + BeginTime, date + BeginBreak) / 60.0
                    WHEN @EndDate >= date + EndBreak THEN
                        DATEDIFF(minute, date + BeginTime, @EndDate) / 60.0 - DATEDIFF(minute, BeginBreak, EndBreak) / 60.0
                    ELSE
                        DATEDIFF(minute, date + BeginTime, @EndDate) / 60.0
                END

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

/*
SELECT * FROM dbo.fn_BusinessHoursInTimeSpan('ID', '2014-01-30 12:30', '2014-02-01 14:30')
*/

If you need to sum, here it is:

ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSum
(
    @CalID VARCHAR(2),
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @Hours FLOAT
    SELECT @Hours = ROUND(SUM(WorkedHours),2) from dbo.[fn_BusinessHoursInTimeSpan](
        @CalID, @StartDate, @EndDate)
    RETURN @Hours
END

/*
SELECT dbo.fn_BusinessHoursInTimeSpanSum('ID', '2010-10-06 08:45', '2010-10-06 19:30') AS TotalHoursWorked
*/ 

Enjoy!