Please check if the performance is sufficient:
SELECT empid
,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
FROM emplog b
WHERE b.empid = a.empid
AND b.time > a.time
AND b.state = 1
)))) date_worked
FROM emplog a
WHERE state=0
GROUP BY empid;
Edit
To aggregate per day:
SELECT empid
,DATE(time) day
,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
FROM emplog b
WHERE b.empid = a.empid
AND b.time > a.time
AND b.state = 1
)))) date_worked
FROM emplog a
WHERE state=0
GROUP BY empid, DATE(time);
Here's one method of getting running totals after reset. There are probably others out there. Here is your test data:
CREATE TABLE user20907 (
ID INTEGER NULL,
CUSTOMERID INTEGER NULL,
AMOUNT INTEGER NULL,
RESETYN INTEGER NULL
);
BEGIN TRANSACTION;
INSERT INTO user20907 VALUES (1,111,5,0);
INSERT INTO user20907 VALUES (2,111,6,0);
INSERT INTO user20907 VALUES (3,111,7,1);
INSERT INTO user20907 VALUES (4,111,8,0);
INSERT INTO user20907 VALUES (5,111,9,0);
INSERT INTO user20907 VALUES (6,111,4,1);
INSERT INTO user20907 VALUES (7,111,7,0);
INSERT INTO user20907 VALUES (8,111,9,0);
COMMIT TRANSACTION;
First get a running total of the ResetYN
column. I'm going to call it SUM_OF_RESETYN
. In an outer query you can partition by both CustomerID
and SUM_OF_RESETYN
. For the reset running total, instead of taking Amount
directly you can do a case statement similar to what you already have:
SELECT
t.ID
, CUSTOMERID
, AMOUNT
, RESETYN
, RUNNINGTOTAL
, SUM(CASE WHEN RESETYN = 1 THEN 0 ELSE AMOUNT END) OVER (PARTITION BY CUSTOMERID, SUM_OF_RESETYN ORDER BY ID) RESETRUNNINGTOTAL
FROM
(
SELECT
ID
, CUSTOMERID
, AMOUNT
, RESETYN
, SUM(AMOUNT) OVER (PARTITION BY CUSTOMERID ORDER BY ID) RUNNINGTOTAL
, SUM(RESETYN) OVER (PARTITION BY CUSTOMERID ORDER BY ID) SUM_OF_RESETYN
FROM user20907
) t;
I ran that query and the results matched yours, except that I did not have a NULL value for RESETRUNNINGTOTAL
for ID
1. Was that intentional? If so it should be easy to integrate that bit of logic into my query.
Best Answer
For SQL-Server & Postgres:
For MySql:
SQL-Server dbfiddle here
MySQL dbfiddle here
Postgres dbfiddle here