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.