Get data based on exclude logic-CodePudding

Added Table for more context, i need data based below two conditions

Max (activity_date) with Open_amt <> 0 and
Exclude rows if Max (activity_date) with Open_amt = '0' and State = 'closed'

Inv #	Account #	Activity	State	Open_Amt	Last Activity Dt	Seq	St_date
123	Customer1	Act-1	Issued	12.50	2022-02-18	455	2022-01-04
123	Customer1	Act-2	Closed	0.00	2022-03-05	567	2022-01-04
345	Customer2	Act-1	Issued	15.00	2022-02-18	467	2022-01-12
345	Customer2	Act-2	Issued	09.35	2022-02-25	488	2022-01-12
678	Customer3	Act-1	Issued	30.50	2022-03-20	589	2022-01-23
678	Customer3	Act-2	Closed	00.00	2022-03-30	623	2022-01-23
678	Customer3	Act-3	Issued	30.50	2022-04-02	788	2022-01-23
678	Customer3	Act-4	Issued	05.50	2022-04-10	988	2022-01-23

for above table below is the output

Inv #	Account #	Activity	State	Open_Amt	Last Activity Dt	Seq	St_date
345	Customer2	Act-2	Issued	09.35	2022-02-25	488	2022-01-12
678	Customer3	Act-4	Issued	05.50	2022-04-10	988	2022-01-23

CodePudding user response：

I'm not sure if you are really running mysql given the column names. But here it goes the query in mysql:

with
 latest as (select Inv, Max(Last_Activity_Dt) as date
    from tbl group by (Inv))

select t.*
from latest as lt
 left join tbl as t
 on lt.Inv = t.Inv
 and lt.date = t.Last_Activity_Dt
where t.state <> 'Closed' and Open_Amt <> 0;

It uses CTE which available on mysql 8. The CTE is used to get all of the latest record for a given customer based on their Inv.

Fiddle: https://www.db-fiddle.com/f/5fAJen86r2qReZYHdhxtcj/0