Display group having certain value only on max date and not in other entries of group

Question

I am looking to display the group which is grouped on id column where trans_cd='Audit' only on max trans_proc_dt of that group. There should not be any other trans_cd='Audit' execpt for max trans_proc_dt.

    ID  TRANS_PROC_DT   TRANS_CD    TRANS_AMT
165     5/13/2020       Renewal         553
165     10/22/2020      Cancellation    -376
165     11/24/2020      Audit   3
165     6/2/2021        Change  0
165     6/2/2021        Audit   -7
165     6/3/2021        Audit   0
497     5/1/2020        Renewal 1394
497     1/11/2021       Cancellation    -578
497     2/10/2021       Audit   -3
497     4/28/2021       Audit   76
497     5/12/2021       Audit   -73
497     6/2/2021        Change  0
511     4/27/2020       Renewal 4409
511     7/30/2020       Change  0
511     10/5/2020       Cancellation    -2558
511     2/18/2021       Audit   2806
577     5/15/2020       Renewal 829
577     2/12/2021       Audit   -123
577     4/28/2021       Audit   118
577     5/12/2021       Audit   5
577     6/2/2021        Change  0
577     6/2/2021        Audit   -5
577     6/3/2021        Audit   0
577     12/4/2020       Renewal 1996
577     6/2/2021        Change  0
751     5/13/2020       Renewal 1307
751     1/28/2021       Cancellation    -523
751     3/3/2021        Audit   481
751     4/28/2021       Audit   120
751     5/12/2021       Audit   -601
751     6/2/2021        Change  0
751     6/2/2021        Audit   601
751     6/3/2021        Audit   0
984     5/13/2020       Renewal 1081
984     11/2/2020       Change  0
984     6/3/2021        Audit   0

My output should be

ID  TRANS_PROC_DT   TRANS_CD    TRANS_AMT
511     4/27/2020       Renewal 4409
511     7/30/2020       Change  0
511     10/5/2020       Cancellation    -2558
511     1/27/2021       Renewal 4409
511     2/18/2021       Audit   2806
984     5/13/2020       Renewal 1081
984     11/2/2020       Change  0
984     6/3/2021        Audit   0

I can't think how to go about getting my result set.

Answer 1

You can use analytic functions to calculate absolute maximum date and minimum date of Audit code per group, then compare them: if no more such transactions occurred on other dates, they should be equal.

with a(ID, TRANS_PROC_DT, TRANS_CD, TRANS_AMT) as (
select 165, '5/13/2020', 'Renewal', 553 from dual union all
select 165, '10/22/2020', 'Cancellation', -376 from dual union all
select 165, '11/24/2020', 'Audit', 3 from dual union all
select 165, '6/2/2021', 'Change', 0 from dual union all
select 165, '6/2/2021', 'Audit', -7 from dual union all
select 165, '6/3/2021', 'Audit', 0 from dual union all
select 497, '5/1/2020', 'Renewal', 1394 from dual union all
select 497, '1/11/2021', 'Cancellation', -578 from dual union all
select 497, '2/10/2021', 'Audit', -3 from dual union all
select 497, '4/28/2021', 'Audit', 76 from dual union all
select 497, '5/12/2021', 'Audit', -73 from dual union all
select 497, '6/2/2021', 'Change', 0 from dual union all
select 511, '4/27/2020', 'Renewal', 4409 from dual union all
select 511, '7/30/2020', 'Change', 0 from dual union all
select 511, '10/5/2020', 'Cancellation', -2558 from dual union all
select 511, '2/18/2021', 'Audit', 2806 from dual union all
select 577, '5/15/2020', 'Renewal', 829 from dual union all
select 577, '2/12/2021', 'Audit', -123 from dual union all
select 577, '4/28/2021', 'Audit', 118 from dual union all
select 577, '5/12/2021', 'Audit', 5 from dual union all
select 577, '6/2/2021', 'Change', 0 from dual union all
select 577, '6/2/2021', 'Audit', -5 from dual union all
select 577, '6/3/2021', 'Audit', 0 from dual union all
select 577, '12/4/2020', 'Renewal', 1996 from dual union all
select 577, '6/2/2021', 'Change', 0 from dual union all
select 751, '5/13/2020', 'Renewal', 1307 from dual union all
select 751, '1/28/2021', 'Cancellation', -523 from dual union all
select 751, '3/3/2021', 'Audit', 481 from dual union all
select 751, '4/28/2021', 'Audit', 120 from dual union all
select 751, '5/12/2021', 'Audit', -601 from dual union all
select 751, '6/2/2021', 'Change', 0 from dual union all
select 751, '6/2/2021', 'Audit', 601 from dual union all
select 751, '6/3/2021', 'Audit', 0 from dual union all
select 984, '5/13/2020', 'Renewal', 1081 from dual union all
select 984, '11/2/2020', 'Change', 0 from dual union all
select 984, '6/3/2021', 'Audit', 0 from dual
)
, last_dt as (
  select
    a.*
    , min(
       case trans_cd
       when 'Audit'
       then to_date(TRANS_PROC_DT, 'mm/dd/yyyy')
       end
    ) over(partition by id)
    as audit_dt
    , max(to_date(TRANS_PROC_DT, 'mm/dd/yyyy'))
      over(partition by id)
    as max_dt

  from a
)
select
  id
  , trans_proc_dt
  , trans_cd
  , trans_amt
from last_dt
where max_dt = audit_dt
order by id, to_date(TRANS_PROC_DT, 'mm/dd/yyyy')

 ID | TRANS_PROC_DT | TRANS_CD     | TRANS_AMT
--: | :------------ | :----------- | --------:
511 | 4/27/2020     | Renewal      |      4409
511 | 7/30/2020     | Change       |         0
511 | 10/5/2020     | Cancellation |     -2558
511 | 2/18/2021     | Audit        |      2806
984 | 5/13/2020     | Renewal      |      1081
984 | 11/2/2020     | Change       |         0
984 | 6/3/2021      | Audit        |         0

db<>fiddle here