0

I'm trying to create a SQL query to do the following:

A business needs to record the number of calls answered within two hours from instruction. An instruction could be received from an external company at 1630, but as long as the return call is made to the external company by 1030 the next day then that would count as being returned within 2 hours (assuming a working day of 0900-1700). This gets more difficult because working days need to factor in weekends too, a call at 1630 on a Friday can be returned by 1030 on Monday and still count as being returned within 2 hours

I have written the following SQL code so far:

WITH checkcalllogs 
AS (SELECT ( Datediff(hour, Dateadd(s, ( Datediff(s, '19700101', hdc.date09) 
+ hdc.number09 ), '19700101'), 
Dateadd(s, ( Datediff(s, '19700101', cc.origdate) + cc.origtime ),
'19700101')) ) AS CallReturnTime
 FROM   hdcase AS hdc 
 INNER JOIN crmcall AS cc 
 ON hdc.hdcasenum = cc.key1 
 AND hdc.company = cc.company 
 WHERE  callseqnum = 1) 
 SELECT callreturntime AS [Num of Calls Returned <= 2 Hours]
 FROM   checkcalllogs 
 WHERE  callreturntime <= 2

This returns the number of calls returned within 2 hours, but does not apply to business hours or weekends.

I have thought about using Cases to remove the 16 hours of time difference between 17:00 and 09:00am but I am struggling to get the results I am looking for.

What is the best way to configure this SQL query to return calls returned within a 2 hour period during business hours?

Dhwani
  • 7,484
  • 17
  • 78
  • 139
samb90
  • 1,063
  • 4
  • 17
  • 35
  • 3
    Do you have a calendar table? This is highly recommended when working with business hours. In addition to such regular occurrences as weekends, you often have to deal with holidays. – Gordon Linoff May 23 '14 at 11:06
  • I haven't got a Calendar table. I want to get it working without public holidays initially. – samb90 May 23 '14 at 11:10
  • 1
    Agree with @GordonLinoff. They best, and possibly only way to do this is to have a Calendar table containing all the working hours till a safe future date(2 years maybe). Then use this table as reference in your query – A Nice Guy May 23 '14 at 11:11
  • If you will finally challenge to write it in a single query, probably [this post](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) might help you. – xacinay May 23 '14 at 13:33

0 Answers0