MySQL total time is compounding

Question

I have this query,

SELECT
    testTimes.reportId,
    testTimes.userID,
--  diff,
    SUM(diff) AS total
FROM
    (
    SELECT
        start_log.reportID,
        start_log.userID,
        start_log.testID,
        MAX(start_log.eventDateTime) AS start_time,
        end_log.eventDateTime AS end_time,
        TIMESTAMPDIFF(MINUTE,
        MAX(start_log.eventDateTime),
        end_log.eventDateTime) AS diff
    FROM
        testtracker_event AS start_log
    INNER JOIN testtracker_event AS end_log ON
        ( start_log.userID = end_log.userID
        AND start_log.reportID = end_log.reportID
        AND start_log.testID = end_log.testID
        AND end_log.eventDateTime > start_log.eventDateTime)
    WHERE
        (start_log.eventType = 'start'
        OR start_log.eventType = 'resume')
        AND (end_log.eventType = 'finish'
        OR end_log.eventType = 'pause')
        AND start_log.reportID = '20466'
    GROUP BY
        start_log.userID,
        start_log.testID,
        start_log.eventDateTime,
        start_log.reportID
    ORDER BY
        start_time DESC) testTimes

Which when run against this data set;

|reportID|testID|eventDateTime|userID|eventType|workstation|note|  
|--------|------|-------------|------|---------|-----------|----|  
|20466|1|2018-04-26 14:41:42|msharpe|start| |           |  
|20466|1|2018-04-26 15:17:10|msharpe|finish| |            |  
|20466|1|2018-04-26 15:17:20|msharpe|reset| |"MIS-CLICK"|  
|20466|1|2018-04-26 15:17:21|msharpe|start| |           |  
|20466|1|2018-04-27 08:11:02|msharpe|finish| |            |  
|20466|2|2018-04-26 14:41:43|msharpe|start| |           |  
|20466|2|2018-04-27 08:11:02|msharpe|finish| |            |  
|20466|3|2018-04-26 14:42:15|msharpe|start| |           |  
|20466|3|2018-04-26 15:17:23|msharpe|finish| |            |  
|20466|4|2018-04-26 15:19:25|msharpe|start| |           |  
|20466|4|2018-04-26 15:34:59|msharpe|finish| |            |  
|20466|5|2018-04-26 14:42:21|msharpe|start| |           |  
|20466|5|2018-04-26 15:17:29|msharpe|finish| |            |  
|20466|7|2018-04-26 16:32:57|msharpe|start| |           |  
|20466|7|2018-04-27 08:11:03|msharpe|finish| |            |  
|20466|8|2018-04-26 15:38:29|msharpe|markna| |            |  
|20466|10|2018-04-27 12:05:21|msharpe|start| |           |  
|20466|10|2018-04-27 12:05:22|msharpe|finish| |           |  
|20466|16|2018-04-26 14:42:17|msharpe|start| |           |  
|20466|16|2018-04-26 15:17:28|msharpe|finish| |           |  
|20466|40|2018-04-26 14:42:17|msharpe|start| |           |  
|20466|40|2018-04-26 15:17:26|msharpe|finish| |           |  
|20466|50|2018-04-27 12:05:22|msharpe|start| |           |  
|20466|50|2018-04-27 12:05:23|msharpe|finish| |           |  
|20466|52|2018-04-27 08:50:54|msharpe|start| |           |  
|20466|52|2018-04-27 10:00:27|msharpe|finish| |           |  
|20466|53|2018-04-26 15:58:56|msharpe|start| |           |  
|20466|53|2018-04-26 16:32:56|msharpe|finish| |           |  
|20466|54|2018-04-26 15:38:24|msharpe|markna| |           |

RESULT - 20466  msharpe 3293

I currently get 3,293 minutes as a result, and even with concurrent scheduling and 2 cases where it looks like an even was signed on overnight, I would expect the results to be VERY Roughly 1845

I can see there is some duplication happening and I would guess it has something to do with the join however I lack the experience or knowledge to get the right answer, in this case, answers I have found, I have trouble correlating to this situation.

While I understand some might be tempted to cite an older one of my questions How would I update this MySQL query to get total elapsed time from start, stop, pause and resume events and this question Calculating difference on datetime row betwen rows on the same table as duplicates, I am asking about improving the existing query to not have the compound effect, and/or minimize duplication.

SQLFiddle for good measure, http://www.sqlfiddle.com/#!9/0dd34d/1

Please explain how you arrive at the **(rough) expected result** of 1845. There is no `resume` data by the way — Paul Maxwell, Sep 27 '18 at 03:09
Just tallied it real quick in my head by looking at the data and just rounding, think I missed an overnight while doing it. That's why I specified a VERY Roughly. — Mark Carpenter Jr, Sep 27 '18 at 12:00

Paul Maxwell · Accepted Answer · 2018-09-27T02:43:12.860

In your existing join you are match each start point to multiple end points, hence you are multiplying the total. You need to match each start point to just the next finish point (one start with one finish). Unless you are running a database version that supports lead() over() you can achieve this using a correlated subquery, like this:

SELECT
    start_log.reportID
  , start_log.userID
  , start_log.testID
  , start_log.eventDateTime AS start_time
  , start_log.end_time
  , TIMESTAMPDIFF(MINUTE,start_log.eventDateTime,start_log.end_time) AS diff
FROM (
        select
            s.*
            , (select f.eventDateTime from mytable f
                    WHERE s.userID = f.userID
                    AND s.reportID = f.reportID
                    AND s.testID = f.testID
                    AND f.eventType = 'finish'
                    AND f.eventDateTime > s.eventDateTime
               order by f.eventDateTime
               limit 1
               ) end_time
        from mytable as s
        where s.eventType = 'start'
       ) AS start_log

The result of that query (using supplied sample) is:

       reportID   userID    testID       start_time             end_time         diff  
 ---- ---------- --------- -------- --------------------- --------------------- ------ 
   1      20466   msharpe        1   26.04.2018 14:41:42   26.04.2018 15:17:10     35  
   2      20466   msharpe        1   26.04.2018 15:17:21   27.04.2018 08:11:02   1013  
   3      20466   msharpe        2   26.04.2018 14:41:43   27.04.2018 08:11:02   1049  
   4      20466   msharpe        3   26.04.2018 14:42:15   26.04.2018 15:17:23     35  
   5      20466   msharpe        4   26.04.2018 15:19:25   26.04.2018 15:34:59     15  
   6      20466   msharpe        5   26.04.2018 14:42:21   26.04.2018 15:17:29     35  
   7      20466   msharpe        7   26.04.2018 16:32:57   27.04.2018 08:11:03    938  
   8      20466   msharpe       10   27.04.2018 12:05:21   27.04.2018 12:05:22      0  
   9      20466   msharpe       16   26.04.2018 14:42:17   26.04.2018 15:17:28     35  
  10      20466   msharpe       40   26.04.2018 14:42:17   26.04.2018 15:17:26     35  
  11      20466   msharpe       50   27.04.2018 12:05:22   27.04.2018 12:05:23      0  
  12      20466   msharpe       52   27.04.2018 08:50:54   27.04.2018 10:00:27     69  
  13      20466   msharpe       53   26.04.2018 15:58:56   26.04.2018 16:32:56     34

Whilst further refinements may be needed, this should allow you to move forward.

see: http://rextester.com/CWB43023

MySQL total time is compounding

1 Answers1