Link to home
Create AccountLog in
Avatar of dbrennecke
dbrennecke

asked on

SQL 2005 Determine DueDate while considering business hours and weekends

I am trying to write a function that determines the "due date" of a trouble ticket based on the time it came in, the number of hours until it should be completed, and accounting for working hours and weekends. Can someone help me out with this?

Examples (working hours are 9:00 - 17:00)

Received             Due Hours       Due Date
Monday 10:00              2              Monday 12:00
Monday 16:00              2              Tuesday 10:00
Monday 18:00              2              Tuesday 11:00
Friday    16:00              2              Monday 10:00
Saturday 12:00            2               Monday 11:00
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dbrennecke
dbrennecke

ASKER

This looks really good, but it has a little problem on the weekend. These two return a Tuesday value.

select 'Sat',dbo.DeadLineCalc('2010-1-01 16:00', 2.0)  -- sat, should return Monday 1-3 10:00, but returns 1-4
select 'Sun',dbo.DeadLineCalc('2010-1-2 16:00', 2.0) -- Sun, should return Monday 1-3 10:00, but returns 1-4
Works perfectly