<div>
how many SLA levels do you have ?<br />
<br />
priority = 2 = 4 hours<br />
priority = 3 = 24 hours (easy date arithmetic - just weekends)<br />

</div>


<div>
The sqlteam link looks like it may be helpful but doesn't seem to account for hours, and it is a little confusing - it will require a longer sit down to absorb. As for mark's comment: we have 5 priorities (priority 5 has no SLA - so there are 4 SLA levels). P1 runs on 24 hour clock so no special calculations are needed. &nbsp;Calculating P3 as 24 hours and working only with the dates could possibly work, and something similar could be done with P4, but P2 would require a different approach. If we just leave the hours as they are, a call opened at 6pm would briech at 10pm (instead of 10am the next day). I'd also like to be able to adjust the hours of operation or the SLA levels easily in the future.<br />
&nbsp;<br />
P1 = 2 hours<br />
P2 = 4 hours<br />
P3 = 1 day<br />
P4 = 3 days<br />
P5 = no SLA
</div>


<div>
OK, no problems - might do the function rather than pure query - it can be the home for &quot;hours&quot; and the date arithmetic will get a bit &quot;ordinary&quot; when checking for weekends and day flowovers if we try to do it inline within a query itself .<br />
<br />
One question though, do you have a formal home for Priority with Hours in it already ?<br />
<br />
How about the outcome of the query - if we express in terms of &quot;TIME REMAINING&quot; then positive numbers will be &quot;within&quot; SLA and negative numbers will be &quot;overdue&quot; or exceeding the SLA. <br />
<br />
Sound OK ?
</div>


<div>
My original idea was to use a combination of a function and queries. &nbsp;For maximum flexibility, I think we should follow this scheme:<br />
A function would compare two dates and return the difference, minus non-business hours<br />
(to determine business hours it would consult a table or be defined internally in the function)<br />
The query would use the results of the function to determine if an SLA had been breached based on the priority, etc.<br />
<br />
A table for business hours would be good because then we could get pretty granular like start and end times for each day of the work week and excluded (holiday) days. We could do the same with declared variables inside the function.. it would make it tighter but less flexible.<br />
<br />
To answer your question, currently there is no &quot;home&quot; for hours, or priorities, or business days other than in the ticketing application itself, which is unusable from this end.<br />
<br />
I suppose it would also be possible to pass the working days / hours to the function as a parameter, but then we'd have to keep it pretty simple and would never be able to account for holidays at all.
</div>


<div>
Oh, one more thing, the above loses flexibility of variable start and end times by individual day, but does take into account holidays, weekends etc...
</div>


<div>
<div class="content wysiwyg-content">
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?<br />
<br />
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.<br />
<br />
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.
</div>
</div>


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.

Create a SQL function to calculate &quot;datediff&quot; based on hours of operation

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Create a SQL function to calculate &quot;datediff&quot; based on hours of operation

Create a SQL function to calculate "datediff" based on hours of operation