MySQL - running total by multiple categories

Question

I want to have a running total column categorized by EmployeeID, PayDateStart, JobDate and JobTime, and my codes are the following:

SELECT EmployeeID, 
   PayDateStart, 
   JobDate, 
   JobTime, 
   JobNo, 
   NetHrs,
  (
  SELECT SUM(NetHrs)
  FROM   test2 t2
  WHERE  t1.EmployeeID    =  t2.EmployeeID
  AND    t1.PayDateStart >=  t2.PayDateStart
  AND    t1.PayDateStart <=  t2.JobDate
  AND    t1.JobDate      >=  t2.JobDate 

  ) AS CummNetHrs
FROM test2 t1
ORDER BY EmployeeID, PayDateStart, JobDate, JobTime;

It gives me the output like this:

EmployeeID  PayDateStart  JobDate    JobTime    JobNo     NetHrs    CummNetHrs
666         06/03/16    06/04/16    13:00:00    6651    9.44    9.44
666         06/03/16    06/05/16    09:00:00    6653    8.18    17.62
666         06/03/16    06/07/16    09:00:00    6654    9.97    27.59
666         06/03/16    06/09/16    09:00:00    6661    9.43    37.02
666         06/10/16    06/11/16    09:00:00    6662    12.69   18.84
666         06/10/16    06/11/16    10:00:00    6663    6.15    18.84
666         06/10/16    06/12/16    08:00:00    6664    5.83    24.67
666         06/10/16    06/14/16    09:00:00    6665    7.27    31.94
666         06/10/16    06/15/16    09:00:00    6666    8.48    40.42    
444         5/27/2016   5/27/2016   9:00:00     4441    9.33    9.33
444         5/27/2016   5/28/2016   8:30:00     4442    5.67    15
444         5/27/2016   5/30/2016   9:00:00     4443    9.67    24.67
444         5/27/2016   5/31/2016   9:00:00     4444    12.67   37.34

But for an employee who worked on the same day but multiple times, the running sum code doesn't work out - it shows the same cumulative value, even if I tried with "t1.JobTime >= t2.JobTime":

EmployeeID  PayDateStart    JobDate JobTime     JobNo     NetHrs    CummNetHrs                      
666        6/10/2016    6/11/2016   9:00:00     6665    12.69   18.84
666        6/10/2016    6/11/2016   10:00:00    6664    6.15    18.84

Any advice would be highly appreciated!

I'm really confused about job date versus the pay date columns. This is not a simple cumulative sum. — Gordon Linoff, Apr 17 '17 at 14:23
Try this http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — Teja, Apr 17 '17 at 14:26
This can't really be done in a query accurately. It would be much better (and reliable) if done in the calling application. — Sloan Thrasher, Apr 17 '17 at 14:37

score 0 · Accepted Answer · edited May 23 '17 at 12:02

Please try the following...

SELECT EmployeeID,
       PayDateStart,
       JobDate,
       JobTime,
       JobNo,
       NetHrs,
       (
           SELECT SUM( NetHrs )
           FROM test2 t2
           WHERE t1.EmployeeID   =   t2.EmployeeID
             AND t1.PayDateStart >=  t2.PayDateStart
             AND t1.PayDateStart <=  t2.JobDate
             AND ( t1.JobDate + INTERVAL TIME_TO_SEC( t1.JobTime ) SECOND ) >=  ( t2.JobDate + INTERVAL TIME_TO_SEC( t2.JobTime ) SECOND )
       ) AS CummNetHrs
FROM test2 t1
ORDER BY EmployeeID,
         PayDateStart,
         JobDate,
         JobTime;

To solve this problem I sought out a way of adding the date and time fields together. I found a solution at Add DATE and TIME fields to get DATETIME field in MySQL and have modified your code in line with this solution.

If you have any questions or comments, then please feel free to post a Comment accordingly.

It worked out perfectly! I am more than excited to learn more about your explanation. Thank you so much. — bluebac, Apr 17 '17 at 15:04

MySQL - running total by multiple categories

1 Answers1