CODEWITHSUNDEEP
mysqlsql
Alvin
Alvin

Reputation: 8499

comparing in the same table based on latest creation date

id|code|transaction_date   |amount|status|record_status|creation_date
1 |0001|2021-12-10 00:00:00| 10.00|Succ  |D            |2021-12-10 00:00:00
2 |0001|2021-12-10 00:00:00| 10.00|Fail  |D            |2021-12-11 00:00:00
3 |0001|2021-12-10 00:00:00| 10.00|      |A            |2021-12-12 00:00:00

I want to select recod_status = A, code = 001 and transaction date is 2021-12-10'

SELECT * FROM TestTable
WHERE DATE(alloc.trans_date) IN ('2021-12-10') // Need filter by few dates
AND record_status = 'A'
AND code = '001';

but I only want the record to return if the same code = 001 and transaction date is 2021-12-10' but record_status = 'D', status = 'Fail' and the creation_date is latest.

Example above will return 3rd record because 2nd record which creation_date is the latest and status = 'Fail' and record_status = 'D'.

I just want to compare the latest creation_date.'

I can select the latest transaction where the record_status = 'D'

SELECT * FROM TestTable
WHERE creation_date = (
  SELECT MAX(creation_date)
  FROM TestTable
  WHERE DATE(trans_date) IN ('2021-12-10')
  AND record_status = 'D'
)
AND record_status = 'D'
AND code = '001';

Upvotes: 0

Views: 39

Answers (1)

Akina
Akina

Reputation: 42704

Looks like:

SELECT t1.*  
-- or SELECT t2.* - depends on the task which is unclear for me yet
FROM ( SELECT *
       FROM table
       WHERE DATE(trans_date) = '2021-12-10'
       ORDER BY creation_date DESC LIMIT 1 ) t1
JOIN table t2 ON DATE(t1.trans_date) = DATE(t2.trans_date)
             AND t1.creation_date > t2.creation_date
WHERE t1.record_status = 'A'
  AND t2.record_status = 'D'
  AND NOT EXISTS ( SELECT NULL
                   FROM table t3
                   WHERE DATE(t1.trans_date) = DATE(t3.trans_date)
                     AND t1.creation_date > t3.creation_date
                     AND t3.creation_date > t2.creation_date )

Subquery t1 selects the most last row, and further it is checked for status A. Table copy t2 selects the row which is adjacent to the row selected in subquery (NOT EXISTS checks that there is no any row between), and further it is checked for status D.

Upvotes: 1

Related Questions