By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table myTable(ACTID int, TRID int, CD int, DATE varchar(5))
insert into myTable values( 1, 1, 0, '12/04');-- - To be deleted
insert into myTable values(1, 2, 2, '11/04');-- - To be deleted
insert into myTable values(1, 3, 4, '10/04');-- - Do not delete
insert into myTable values(2, 4, 0, '10/04');-- - To be deleted
insert into myTable values(2, 5, 2, '11/04');-- - Do not delete
insert into myTable values(3, 6, 4, '12/04');-- - Do not delete
insert into myTable values(3, 7, 4, '11/04');-- - To be deleted
7 rows affected
delete a from myTable a inner join myTable b
on (a.ACTID=b.ACTID and b.CD=4 and a.CD<4)
or (a.ACTID=b.ACTID and b.CD=4 and a.TRID<>b.TRID and (a.CD=4 and b.CD=4) and a.DATE<b.DATE)
or (a.ACTID=b.ACTID and b.CD=4 and a.TRID<>b.TRID and((a.CD = 0 and b.CD =2)or(a.CD = 2 and b.CD =0)) and a.DATE<b.DATE)
or (a.ACTID=b.ACTID and a.TRID<>b.TRID and(a.CD in (0,2) and b.CD in (0,2)) and a.DATE<b.DATE)
and not exists (select * from mytable m where m.ACTID=a.ACTID and m.CD=4)
4 rows affected
select * from myTable
| ACTID | TRID | CD | DATE |
|---|---|---|---|
| 1 | 3 | 4 | 10/04 |
| 2 | 5 | 2 | 11/04 |
| 3 | 6 | 4 | 12/04 |