1 Reply Latest reply on May 23, 2018 1:38 PM by Patrick Van Der Hyde

    To find exact hours to update the query

    Vaibhav Adhyapak

      I have a table structure where in a call is raised which is stored as [Open Date Time] and as soon as its raised team responds to query which is stored in another table as [Update date time] which can multiple for the query and we need to consider only min value of all the record i.e first response for the query, I need to make report which shows response time [Open Date Time]- [Update date time] . Working time is 9.30 to 18.30, Any call between this period will be attended in 3hrs.

      One case has come up as below

      [Open Date Time]                 [Update Date Time]               [Response Hours]               Expected

      5/2/2018 3:57:00 PM         5/2/2018 5:37:00 PM              2 hours (Round Off)                 2 hrs

      5/11/2018 06:57:00 PM      5/14/2018 09:37:00 AM        18 hours (Round Off)                1,2 hrs ( as 12 may , 13 may were sat sunday)


      I have seen multiple links which shows saturday sunday exclusion but it doesnt fit in my case i feel.  



      Below is code for Response hours

      IF Datediff('day',[Open Date Time],[Updated Date Time])=0


      ROUND((Datepart('hour',[Updated Date Time])))-

      (IF ROUND(Datepart('hour',[Open Date Time]))<9 then 9 else ROUND(Datepart('hour',[Open Date Time])) end)


      Round (Datediff('day',[Open Date Time],[Updated Date Time])*9 +

      (IF ROUND(Datepart('hour',[Updated Date Time]))>18 then 18 else ROUND(Datepart('hour',[Updated Date Time])) end)-

      (IF ROUND(Datepart('hour',[Open Date Time]))<9

      then 9.30 elseif ROUND(Datepart('hour',[Open Date Time]))>18 then 18 else Datepart('hour',[Open Date Time]) end))