this is the continue question from find out time difference for every user in condition mysql 5.7 this is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=31b3be9d1e2444eb0b32c262176aa4b4 i have this table CREATE TABLE test ( ID INT, user_id INT, createdAt DATE, status_id INT ); INSERT INTO test VALUES (1, 13, ‘2020-01-01’, 8), (2, 13, ‘2020-01-03’, 8), (3, 13, ‘2020-01-06’, 8), […]
- Tags -11, '2020-01-01', '2020-01-02'), '2020-01-03', '2020-01-06', '2020-03-04', '2020-03-10', "14", "2020-03-03", "2020-04-10", 10, 13, 15, 2, 2020-03-02, 2020-04-04, 3, 4, 5, 6, 7, 8, 8); select * from test where status_id != 7 order by createdAt; +----+---------+------------+-----------+ | ID | user_id | createdAt | s, 9, and at least doing 1 transaction again in the time range, and count time difference also before the time range (see users_id 13, createdAt are the date transaction happen, createdAt DATE, DATEDIFF(t2.createdAt, i want counting only his users_id last transaction before time range (on this case, I want to find out time difference for every approval transaction on every repeat users on time range between '2020-02-01' until '2020-04-01', if the users had a transaction before time range, on this case, previous transaction) for these users SELECT t1.user_id, repeat users are the users who doing transaction before the end of the time range, status_id INT ); INSERT INTO test VALUES (1, status_id is the status for the transaction (if the status_Id is 7, t1.createdAt, t1.createdAt) diff -- table for a transaction FROM test t1 -- table for prev. transaction JOIN test t2 ON t1.user_id = t2.user_id, t2.createdAt, t2.status_id) -- get data only for users from prev. query JOIN (SELECT t3.user_id FROM test t3 WHERE t3.status_id != 7 GROU, that users also counting time difference in date '2020-01-01' until '2020-01-03'). what i want is, the expected results was like this : +---------+------------+------------+------+ | user_id | createdAt | createdAt | diff | +---------+--, then the transaction are denied or not approval). so on this case, this is the continue question from find out time difference for every user in condition mysql 5.7 this is my fiddle https://dbfiddle.uk/?rdb, this query count time difference in time range ('2020-02-01' until '2020-04-01') for each users, user_id INT, user_Id is the id of the users who doing the transaction, users are doing approval transaction before '2020-04-01' and at least doing 1 more approval transaction again in between '2020-02-01' and '20, users_id 13 who i want to counting just only time difference in '2020-01-06' until '2020-03-04' because in 06 january 2020 is the date when t