Computing Business Hours

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am. Call Center Hours are 8-5.

Essentially I have a number of different alternatives, and have found some potential solutions, including:

www.dbforums.com/arch/7/2003/9/914261

However, my situation has a couple of additional twists to the standard 8hrs of business M-F. The call center is open different hours depending upon the day of the week. For example, 8-5 M, 10-7 T, 8-5 W Th F, 10-2 Sat, 10-12 Sun

Additionally, I would like to remove Holiday's from the calculation for service level as well.

I have explored a number of different table DTD's, but none seem to be a perfect fit for determining the number of "open" hours between when an interaction arrived, and when it was handled.

The DTD I have for the Holiday table is as follows:

CREATE Table Holidays (HolidayDate DateTime)
GO
Insert Into Holidays (HolidayDate) Values ('12-25-2004')

Please let me know what you feel would be the DTD for storing the business hours and also the query for extracting the number of Open hours between two dates

Thank you in advanceyour original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?|||Originally posted by Paul Young
your original question was

I am attempting to compute Service Levels for an interaction based upon business hours. For example, an email arrives at 4pm and is handled the following day at 10am.

Given your examples of business hours for Monday and Saturday what would your answer look like for the above question?

16 hours for Monday?
0 hours for Saturday?

What happens if one of these days is a holiday?

Based upon the previous example (8-5) the service level would be 3 hrs. Based upon the "flexible" (and actual scenario) it would have been 1 hour. Call center closed at 5pm and opened at 10am on Tuesday.

NET: I am trying to compute the number of hours the call center is open during a range of time.

Thanks|||okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?|||Originally posted by Paul Young
okay, lets stick to Monday then...

if the call is received at 04:00 pm and the call center closes at 05:00pm then there was one hour on Monday that the call could have been worked. The call center opened on Tuesday at 10:00 am and the call was process during the first our hour so add an additional hour, that's two hours in total. I am missing something as I don't see three or one hours as the answer.

I am almost finished with an example but would like to present a close answer. could you help with the above question?

Sorry, the original question posed two scenarios:

Typical 8-5M-F
Reality (variable hours) I.E. Monday8a-5p , 10a-7p T, 8-5 W Th F, 10-2 Sat, 10-12 Sun.

Thus, the 3hrs versus 1hr.

Let's eliminate the "Typical" scenario, as I have examples of that. What I am struggling with is the reality (variable) open hours scenario, and how to best report SLA's when the center is open.

Sorry for the confuzzzzzion :)|||okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||THANK YOU SO MUCH! I haven't fully digested this yet, but at first glance it appears to be EXACTLY what I needed.

Thank you again.

Mike

Originally posted by Paul Young
okay, still not sure I understand how you are calculating days but maybe you can use this:

declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1700)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 12:00'
, @.dtTo = '18-Mar-2004 10:00'
, @.HolidayCount = 0
, @.Hours = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--
if datediff(day,@.dtFrom,@.dtTo) = 0 begin
set @.Hours = datediff(hour,@.dtFrom,@.dtTo)
end else begin
--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'
--
-- Calculate the number of hours for the day the problem was resolved
--
set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours'

end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours'

this is NOT the optimal code but any changes made would depend on your implamintation. I hope this get you going in the right direction.|||I tried to keep this overly simple and did not worry about efficiency. My goal was to get you thinking about the problem in a diffrent light. If you have a question about any section drop me a line or post it here.

Please post back with your final solution.|||Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

--okay, still not sure I understand how you are calculating days but maybe you can use this:

--code:------------------------
declare @.HolidayCalendar table(Holiday datetime)
declare @.BusinessHours table(DOW tinyint, OpenAt smallint, CloseAt smallint)

declare @.dtFrom datetime
, @.dtTemp datetime
, @.dtTo datetime
, @.DOW tinyint
, @.HolidayCount tinyint
, @.Hours tinyint
, @.Seconds int

insert into @.HolidayCalendar values('17-Mar-2004')
insert into @.BusinessHours values(1, 1000, 1200)
insert into @.BusinessHours values(2, 0800, 1700)
insert into @.BusinessHours values(3, 1000, 1900)
insert into @.BusinessHours values(4, 0800, 1700)
insert into @.BusinessHours values(5, 0800, 1730)
insert into @.BusinessHours values(6, 0800, 1700)
insert into @.BusinessHours values(7, 1000, 1400)

select @.dtFrom = '17-Mar-2004 07:00:00' --3
, @.dtTo = '18-Mar-2004 07:00:00' --7
, @.HolidayCount = 0
, @.Hours = 0
, @.Seconds = 0

select ' Start', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'

--
-- If the problem was solved in one day then only need to calculate hours for a single day
--

--
-- Test for @.dtFrom being a holiday, whould need to move time to start of first business day after the holiday
--
if exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
while exists(select * from @.HolidayCalendar where convert(varchar(10),@.dtFrom,120) = Holiday) begin
--
-- Move @.dtFrom 1 day closer to a business day
--
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end
end

select ' @.dtFrom = Holiday', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being after business hours, would want to move to start of next day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) > (CloseAt/100)) begin
set @.dtFrom = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
end

select ' @.dtFrom after hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Test for @.dtFrom being before business hours, would want to move to start of this business day.
--
if exists(select * from @.BusinessHours where DOW = datepart(weekday,@.dtFrom) and datepart(hour,@.dtFrom) < (OpenAt/100)) begin
select @.dtFrom = dateadd(hour,(OpenAt/100),convert(varchar(10),@.dtFrom,120))
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end

select ' @.dtFrom before hours', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the ploblem was submmited
-- provided the adjusted dates, @.dtFrom & @.dtTo are not on the same day
--
if datediff(day,@.dtFrom,@.dtTo) > 0 begin
set @.dtTemp = convert(varchar(10),@.dtFrom,120)
select @.Hours = CASE WHEN (datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp)) < 0) Then 0
ELSE datediff(hour,@.dtFrom,dateadd(hour,(CloseAt/100),@.dtTemp))
END,
@.Seconds = CASE WHEN (datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp)) < 0) THEN 0
ELSE datediff(ss,@.dtFrom,dateadd(minute, Floor(CloseAt/100 * 60) + Right(CloseAt, 2),@.dtTemp))
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtFrom)
end
select ' Hours for 1st day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'
--
-- Step through each day, if it is a holiday skip else add the number of business hours for that day
--
set @.dtTemp = dateadd(day,1,convert(varchar(10),@.dtFrom,120))
while datediff(day,@.dtTemp,@.dtTo) > 0 begin
if not exists(select * from @.HolidayCalendar where Holiday = @.dtTemp) begin
select @.Hours = @.Hours + (CloseAt - OpenAt) / 100,
@.Seconds = @.Seconds + Floor((CloseAt - OpenAt) / 100 * 60 * 60) + (Right((CloseAt - OpenAt), 2) * 60)
from @.BusinessHours
where DOW = datepart(weekday,@.dtTemp)
end
set @.dtTemp = dateadd(day,1,@.dtTemp)
end

select 'Hours for days between', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'
--
-- Calculate the number of hours for the day the problem was resolved
--
Declare @.SubResult int
Declare @.SubDate datetime
Declare @.SubREsult2 int

set @.dtTemp = convert(varchar(10),@.dtTo,120)
select @.Hours = @.Hours + CASE WHEN datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo) < 0 THEN 0
ELSE datediff(hour,dateadd(hour,(OpenAt/100),@.dtTemp),@.dtTo)
END,
@.Seconds = @.Seconds + CASE WHEN datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo) < 0 THEN 0
ELSE datediff(ss,dateadd(ss, Floor((OpenAt/100 * 60 * 60)) + (Right(OpenAt, 2) * 60), @.dtTemp), @.dtTo)
END
from @.BusinessHours
where DOW = datepart(weekday,@.dtTo)
select ' Hours for Lst day', @.dtFrom as '@.dtFrom', @.dtTemp as '@.dtTemop', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds as 'Seconds'

--end

select ' End', @.dtFrom as '@.dtFrom', @.dtTo as '@.dtTo', @.Hours as 'Hours', @.Seconds As 'Seconds'|||Originally posted by BMWM3LUV
Paul,

Thanks a million. I doubt I would have ever come up with the solution that you presented. I have tweeked it a little, and am posting the code change.

Biggest Delta, is I removed the same day logic. This was making the assumption that an interaction starting and ending the same day was the duration of the interaction. This would have "dinged" the call center in three instances. Interaction came in before start of day and handled during day. If for some reason the day was a Holiday. Or, if the interaction was handled after business hours. (That time after close, but before the workers go home).

Additionally, I converted the computations into Seconds.

Finally, I handled when the interaction was handled with NO open business hours. I.E. Came in after close and handled before open.

However, the logic you provided was invaluable.

Thanks agian

HERE'S THE CODE

Glad to help! I have been told I am not quite right, maybe that is why I enjoy working this type of problem.|||If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike|||Originally posted by BMWM3LUV
If it makes you sleep better, you are ALL RIGHT in my book. I searched/begged/and pleaded for this solution for months.

Finally, my work told me I didn't have any more time to "research" this. So, I ultimately had to tell the customer that this report was going to cost them 12k.

Now that I have the query, I can put together the front end and re-sell it! If I turn a profit, I will make sure to share the wealth.

Mike

Cool, Do you own an M3 or lust after one? If you you do then you own me a ride! I am a Mercedes Benz guy but wouldn't pass up a ride in a bemer!|||Own 98 M3 4-door. Incredible car. Joys of being a computer geek.|||How about just as select statement? Also independent of datefirst setting.

CREATE TABLE WorkHours(
DayNum tinyint PRIMARY KEY CHECK(DayNum BETWEEN 1 AND 7),
StartTime smalldatetime CHECK(StartTime BETWEEN 0 AND 1),
EndTime smalldatetime CHECK(EndTime BETWEEN 0 AND 1),
DayName char(9))

insert WorkHours
select 1, '8:00 AM', '5:00 PM', 'Monday'
union all select 2, '10:00 AM', '7:00 PM', 'Tuesday'
union all select 3, '8:00 AM', '5:00 PM', 'Wednesday'
union all select 4, '8:00 AM', '5:00 PM', 'Thursday'
union all select 5, '8:00 AM', '5:00 PM', 'Friday'
union all select 6, '10:00 AM', '2:00 PM', 'Saturday'
union all select 7, '10:00 AM', '12:00 PM', 'Sunday'

CREATE TABLE Holidays(
Holiday datetime PRIMARY KEY CHECK(Holiday = CONVERT(char(8),Holiday,112)),
Descr char(12))

insert Holidays
select '20040101','New Years'
union all select '20040119', 'ML King Day'
union all select '20040216', 'President''s'
union all select '20040411', 'Easter'
union all select '20040531', 'Memorial'
union all select '20040704', 'Independence'
union all select '20040906', 'Labor'
union all select '20041011', 'Columbus'
union all select '20041111', 'Veteran''s'
union all select '20041125', 'Thanksgiving'
union all select '20041225', 'Christmas'

CREATE PROC p_ProcessHours @.Opened datetime, @.Closed datetime, @.Hours int OUTPUT AS
IF @.Opened > @.Closed RETURN -1
SELECT @.Hours = --weeks between dates
(SELECT (DATEDIFF(d,@.Opened,@.Closed)/7)*SUM(DATEDIFF(hh,StartTime,EndTime))
FROM WorkHours)
- --holidays
(SELECT ISNULL(SUM(DATEDIFF(hh,StartTime,EndTime)),0)
FROM Holidays h JOIN WorkHours w ON DATEDIFF(d,0,h.Holiday)%7+1 = DATEDIFF(d,0,DayNum)
WHERE Holiday > @.Opened AND Holiday < @.Closed)
+ --hours of dates in same week
(SELECT SUM(DATEDIFF(hh,StartTime,EndTime))
FROM
(SELECT DayNum, StartTime, EndTime
FROM WorkHours
UNION ALL
SELECT DayNum+7,StartTime,EndTime
FROM WorkHours)x
WHERE DayNum BETWEEN DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(d,0,@.Closed)%7
+ CASE WHEN DATEDIFF(d,0,@.Opened)%7 > DATEDIFF(d,0,@.Closed)%7 THEN 8 ELSE 1 END)
- --hours not in opened date
ISNULL(
(SELECT DATEDIFF(hh,StartTime,CASE WHEN CONVERT(varchar,@.Opened,14) < EndTime THEN CONVERT(varchar,@.Opened,14) ELSE EndTime END)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Opened)%7+1 AND DATEDIFF(hh,StartTime,CONVERT(varchar,@.Opened,14)) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Opened,112))),0)
- --hours not in closed date
ISNULL(
(SELECT DATEDIFF(hh,CASE WHEN CONVERT(varchar,@.Closed,14) > StartTime THEN CONVERT(varchar,@.Closed,14) ELSE StartTime END,EndTime)
FROM WorkHours
WHERE DayNum = DATEDIFF(d,0,@.Closed)%7+1 AND DATEDIFF(hh,CONVERT(varchar,@.Closed,14),EndTime) > 0
AND NOT EXISTS
(SELECT *
FROM Holidays
WHERE Holiday = CONVERT(char(8),@.Closed,112))),0)