quote:

---

Originally posted by FSA

I know the thread is dated but is it possible to subtract holidays from the start and end dates. Can someone show me how to add that to the query?

KHTAN I really appreciated the solution you posted!

Thanks.

---

quote:

---

Originally posted by thecrow55


I know this Thread is a bit old... but an additional tip to the well presented sample would be appreciate it.

When the start_date and end_date are on the same day, you are using just datediff no matter if the start or end dates are outside business hours.

Could it be possible this sample: the start_date is at 6am but business hours start at 8am. We are at 10am. So the result should be 2 hours and not 4.

Thanks in advance for your help!

---

quote:

---

do you have a holiday table ?


from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
left join holiday h
on d.DATE = h.DATE
where h.DATE is NULL

---

quote:

---

Originally posted by gooral1

Hi KHTAN!

quote:

---

do you have a holiday table ?


from F_TABLE_DATE(@start_date, @end_date) d inner join ttr_calendar c
on d.WEEKDAY_NAME_LONG = c.day_name
left join holiday h
on d.DATE = h.DATE
where h.DATE is NULL

---

Code works great but I have a problem with the elimination of such holidays Christmas and Easter. How exactly should I entered a table with Christmas?

thx
gooral

---

CREATE TABLE dbo.WorkCalendar
(
-- we can use the date as primary key
dt SMALLDATETIME PRIMARY KEY CLUSTERED,

-- computed column; very deterministic
isWeekDay AS CONVERT(BIT, CASE
WHEN datepart(dw, dt) IN (6,7)
THEN 0 ELSE 1 END),

-- not a computed column, because we may
-- need to override
isWorkDay BIT DEFAULT 1
);
GO

-- populate the table

DECLARE @dt SMALLDATETIME;
SET @dt = '20070101';
WHILE @dt <= '20151231'
BEGIN
INSERT dbo.WorkCalendar(dt) SELECT @dt;
SET @dt = @dt + 1;
END

-- now we have to set the weekends back to 0
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWeekday = 0;

-- now, let's populate some holidays
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWorkDay = 1
AND dt IN
(



'20090101', --New Year,
'20090412', --Easter Day,

'20100101', --New Year,
'20100404' --Easter Day,


);

quote:

---

Originally posted by FSA

Hi KHTAN!

I was hoping you'd read my request. I implemented your solution and it works. However, when I use the code in a function and do the following:

SELECT dbo.fn_workinghour('2007-09-21 23:56:48.000','2007-09-23 23:49:44.000')

I get -5.95.

Should I input the dates differently?

My code is like this:

SELECT dbo.fn_Workinghour(OPEN_TIME, CLOSE_TIME)
FROM MyActivityTable

Both OPEN_TIME and CLOSE_TIME are DATETIME fields.

Thank you for your help!

---

quote:

---

Originally posted by jjm2958

quote:

---

Originally posted by thecrow55


I know this Thread is a bit old... but an additional tip to the well presented sample would be appreciate it.

When the start_date and end_date are on the same day, you are using just datediff no matter if the start or end dates are outside business hours.

Could it be possible this sample: the start_date is at 6am but business hours start at 8am. We are at 10am. So the result should be 2 hours and not 4.

Thanks in advance for your help!

---

I know this thread is years old now, but I stumbled upon it and the calendar table at the beginning and the functions have helped me get to 99% completion. The only sticking point left is calculating the business hours when the start date and end date are on the same day. I see where in the first case statement you are just doing a datediff and that would be fine if the start date is always after the start of business hours adn the end date is always before the end of business hours. Consider the following:

Business hours defined as 8:00 AM to 5:00 PM Monday through Friday.
Ticket open datetime (for example) is 7:00 AM Monday and tech response time is 9:00 AM Monday. The duration I'm expecting is 1 hour since business hours don't start until 8am. The above referenced function gives a total_hours of 2. Any other test cases (when teh start date and end date are on different days) works fine.

Is anyone still available to look at this and provide any insight?

---