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'

      ELSEIF DATEPART('hour',[TIME_STAMP])<10 THEN STR(DATEPART('hour',[TIME_STAMP]))

      ELSEIF DATEPART('hour',[TIME_STAMP])>12 THEN STR(DATEPART('hour',[TIME_STAMP])-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?

       

      Thanks

      Tracey Stone