CODEWITHSUNDEEP
mysql
Mrugesh
Mrugesh

Reputation: 4517

sql query to get the transaction for all the users having specific timestamp

I have sql table structure as follows:

id INT(11),
amount DOUBLE(10,2),
createdAt DATETIME,
user_id INT(11),
tx_status INT(11).

value of tx_status can be 1 (credit) , 2 (debit) and 3(settled). Now what I want is , I want all the latest transactions of all the users where transaction createdAt is greater than last createdAt when tx_status = 3 for particular user.

The data is as shown below :

id .  amount . createdAt .          user_id .      tx_status
112   500 .    2019-05-15 00:30:40  345 .          1
113 . 300      2019-05-16 06:45:29  345 .          2
116 . 600 .    2019-05-16 06:45:33  345 .          3
119 . 567 .    2019-05-17 07:56:55  345 .          1
121   500 .    2019-05-18 00:30:40  346 .          1
123 . 300      2019-05-19 06:45:29  346 .          2
124 . 600 .    2019-05-20 06:45:33  346 .          3
126 . 567 .    2019-05-21 07:56:55  346 .          1

The output should be :

id .  amount . createdAt .          user_id .      tx_status
119 . 567 .    2019-05-17 07:56:55  345 .          1
126 . 567 .    2019-05-21 07:56:55  346 .          1

Upvotes: 0

Views: 350

Answers (1)

Nick
Nick

Reputation: 147206

This query will give you the results you want. It uses a derived table of the maximum times at which settlement occurred, using conditional aggregation to ensure that if no settlements have occurred, that time is '1970-01-01' (so that all records will be returned).

SELECT t1.*
FROM transactions t1
JOIN (SELECT user_id, MAX(CASE WHEN tx_status = 3 THEN createdAt ELSE '1970-01-01' END) AS last_settlement
      FROM transactions t2
      GROUP BY user_id) t2 ON t2.user_id = t1.user_id AND t2.last_settlement < t1.createdAt

Output (for your sample data)

id  amount  createdAt           user_id tx_status
119 567     2019-05-17 07:56:55 345     1   
126 567     2019-05-21 07:56:55 346     1

Demo on dbfiddle

Upvotes: 1

Related Questions