We have a helpdesk ticketing software database on a SQL 2000 server. We are trying to query to see if an SLA has passed based on the priority of the ticket, but we want this calculation based on 7am - 7 pm Mon - Fri. We were thinking we would have to create a function for this, but don't know how to go about it. Any ideas?
example: a call is logged on wednesday at 6pm and is resolved on thursday at 8:00am the difference shoudl be 2 hours, not 14. If the priority was a 2 (4 hour SLA) then the SLA would be met.
example 2: a call was logged on friday at 2pm and resolved on monday at 10am. Priority 3 is one business day, so it should be met.