I have this query that I was able to create from searching through SOF.
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'
AND end_log.eventType = 'finish'
AND start_log.testID = 2
AND start_log.userID = '$user'
GROUP BY
start_log.userID,
start_log.testID,
start_log.eventDateTime,
start_log.reportID
ORDER BY
-- userID DESC,
start_time DESC
Works as expected, the query returns the difference in time from the start_time and end_time as the diff column. I now would like to expand this query to take into account pause and resume events I'm just having trouble understanding how I would take those into account with the current query. I could see expanding the select to account for pause and resume with columns like pause_time and resume_time except how would I account for a case that might have multiple pause and resume events? I would also venture to guess I might have to do some additional calculations to get the actual elapsed time, not sure how I would reference those either.
Any help would be much appreciated.
Sample Data
reportID|testID|eventDateTime |eventType
--------|------|-------------------|----------
11881 | 2 |2013-08-16 14:19:09|start
11910 | 2 |2013-08-16 14:48:04|start
11910 | 2 |2013-08-16 14:55:08|finish
11891 | 2 |2013-08-16 15:01:01|start
11891 | 2 |2013-08-16 15:25:42|finish
11888 | 2 |2013-08-16 16:20:27|start
11888 | 2 |2013-08-16 16:51:16|finish
11889 | 2 |2013-08-19 09:00:13|start
11889 | 2 |2013-08-19 10:31:17|finish
11905 | 2 |2013-08-19 12:10:00|start
11898 | 2 |2013-08-19 12:12:02|start
11898 | 2 |2013-08-19 12:19:28|finish
11905 | 2 |2013-08-19 12:27:21|finish
11880 | 2 |2013-08-19 16:40:26|start
11880 | 2 |2013-08-19 16:40:27|finish
11895 | 2 |2013-08-19 16:42:58|start
11895 | 2 |2013-08-19 16:55:14|finish
11908 | 2 |2013-08-20 09:46:11|start
11908 | 2 |2013-08-20 09:56:20|finish
11862 | 2 |2013-08-20 10:20:11|start
11862 | 2 |2013-08-20 10:29:44|finish
11911 | 2 |2013-08-20 11:31:33|start
11911 | 2 |2013-08-20 11:59:15|finish
11913 | 2 |2013-08-20 12:44:07|start
11913 | 2 |2013-08-20 13:32:52|finish
11928 | 2 |2013-08-20 14:28:38|start
11928 | 2 |2013-08-20 14:40:00|finish
11836 | 2 |2013-08-20 16:26:03|start
11887 | 2 |2013-08-20 16:30:36|start
11836 | 2 |2013-08-20 16:43:53|finish
11887 | 2 |2013-08-20 16:58:44|pause
11902 | 2 |2013-08-22 14:51:53|start
11902 | 2 |2013-08-22 15:01:05|finish
11912 | 2 |2013-08-22 15:01:43|start
11912 | 2 |2013-08-22 15:12:21|finish
11930 | 2 |2013-08-22 16:09:22|start
11814 | 2 |2013-08-14 10:39:07|finish
11930 | 2 |2013-08-23 09:55:14|finish
11914 | 2 |2013-08-26 14:55:04|start
11914 | 2 |2013-08-26 15:19:48|pause
11940 | 2 |2013-08-27 07:44:12|start
11940 | 2 |2013-08-27 07:55:52|finish
11914 | 2 |2013-08-27 09:13:28|resume
11914 | 2 |2013-08-27 09:13:44|pause
11914 | 2 |2013-08-27 09:15:56|resume
11914 | 2 |2013-08-27 09:30:12|finish
Dropped userId column form the sample to protect identities.
Not the expected result for reportID - 11914
reportID|testID|start|end|diff
--------|------|-----|---|-----
11914 | 2 |2013-08-26 14:55:04|2013-08-27 09:30:12|1115
Expected result for reportID - 11902
reportID|testID|start|end|diff
--------|------|-----|---|-----
11902 | 2 | 2013-09-10 14:26:53 | 2013-09-10 14:45:36 | 18
