1 Reply Latest reply on Jun 22, 2016 8:37 PM by swaroop.gantela

    Need help with summing up total time differences

    Tracey Stone

      Hello, I have an issue that I can't figure out how to do. I have a set of data that contains scan times for an operator and I need to total those time difference and subtract 40 for lunch and breaks to get a total time worked for that operator. The data set contained date and time so I strip the date with a DataPart statement as shown. I think it needs to be in military time but not sure how to do that. Any help?


      IF DATEPART('hour',[TIME_STAMP]) = 0 THEN '12'



      ELSE STR(DATEPART('hour',[TIME_STAMP])) END //hour

      + ':' +

      IF DATEPART('minute',[TIME_STAMP])<10 THEN '0'+STR(DATEPART('minute',[TIME_STAMP]))

      ELSE STR(DATEPART('minute',[TIME_STAMP])) END //minute


      IF DATEPART('second',[TIME_STAMP])<10 THEN '0'+STR(DATEPART('second',[TIME_STAMP]))

      ELSE STR(DATEPART('second',[TIME_STAMP])) END //second


      Once I did that I was able to see all the scan times for the operators. As shown in the attachement. But I don't know how to calucalate the total time for the operator. I looked at datediff but I am not sure how that would take the difference between scan times, add them up and subtract 40. And then I have the issue of the last scan of the day for that operator. There is no other record for the day to take the difference from. Any help?



      Tracey Stone