I am working on generating a report in SQL Server 2005 Reporting Services that tracks performance against various SLAs for help desk tickets in a tracking system.
Two of our SLAs involve measuring the time to respond to a ticket (difference between a time received field and time responded to field) based on whether it was received during or outside of business hours. Tickets must be responded to within 1 hour if received between 9 AM and 5 PM on a working day and by 10 AM the following working day (Monday through Friday) if received outside of that time frame or on a weekend.
Is there some way within the SQL query in the report itself or to call a stored procedure of some type that can evaluate whether or not the difference between the time received and time responded (two separate fields) to according to the SLAs outlined above? It would be good if it could provide or return a true/false value.