Results 1 to 14 of 14
Thread: Computing Business Hours

031604, 11:00 #1Registered User
 Join Date
 Mar 2004
 Posts
 7
Unanswered: 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 85.
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 MF. The call center is open different hours depending upon the day of the week. For example, 85 M, 107 T, 85 W Th F, 102 Sat, 1012 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 ('12252004')
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 advance

031604, 12:28 #2Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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?Paul Young
(Knowledge is power! Get some!)

031604, 13:12 #3Registered User
 Join Date
 Mar 2004
 Posts
 7
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?
NET: I am trying to compute the number of hours the call center is open during a range of time.
Thanks

031604, 13:37 #4Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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?Paul Young
(Knowledge is power! Get some!)

031604, 15:49 #5Registered User
 Join Date
 Mar 2004
 Posts
 7
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 85MF
Reality (variable hours) I.E. Monday8a5p , 10a7p T, 85 W Th F, 102 Sat, 1012 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

031704, 10:53 #6Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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 insert into @HolidayCalendar values('17Mar2004') 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 = '17Mar2004 12:00' , @dtTo = '18Mar2004 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'
Paul Young
(Knowledge is power! Get some!)

031804, 11:54 #7Registered User
 Join Date
 Mar 2004
 Posts
 7
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:
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 insert into @HolidayCalendar values('17Mar2004') 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 = '17Mar2004 12:00' , @dtTo = '18Mar2004 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'

031804, 15:27 #8Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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.Last edited by Paul Young; 031804 at 15:30.
Paul Young
(Knowledge is power! Get some!)

031904, 10:08 #9Registered User
 Join Date
 Mar 2004
 Posts
 7
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('17Mar2004')
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 = '17Mar2004 07:00:00' 3
, @dtTo = '18Mar2004 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'

031904, 10:17 #10Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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
Paul Young
(Knowledge is power! Get some!)

031904, 10:39 #11Registered User
 Join Date
 Mar 2004
 Posts
 7
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 resell it! If I turn a profit, I will make sure to share the wealth.
Mike

031904, 10:42 #12Registered User
 Join Date
 Feb 2002
 Location
 Houston, TX
 Posts
 809
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 resell it! If I turn a profit, I will make sure to share the wealth.
MikePaul Young
(Knowledge is power! Get some!)

031904, 10:46 #13Registered User
 Join Date
 Mar 2004
 Posts
 7
Own 98 M3 4door. Incredible car. Joys of being a computer geek.

031904, 16:41 #14Registered User
 Join Date
 Mar 2004
 Posts
 45
How about just as select statement? Also independent of datefirst setting.
Code: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)
Hans.