I again modified the SQL and here are they ...
Create holiday table:
CREATE TABLE [dbo].[Holiday]( [CalID] varchar(2),Here where you define the calendar. If there are many different calendars, you might want to create table for them?
[Holiday] [smalldatetime] NULL)
GO
INSERT INTO Holiday VALUES ('ID', '2014-01-01')
ALTER FUNCTION [dbo].[fn_CalendarTable]Here the main part, calculate working hours:
(@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')
*/
ALTER FUNCTION [dbo].[fn_BusinessHoursInTimeSpan]If you need to sum, here it is:
(@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')
*/
ALTER FUNCTION dbo.fn_BusinessHoursInTimeSpanSumEnjoy!
(
@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
*/