Delete records which have been updated post the voidance

Question

Hi All,

Hope you are doing well! A combination of ctextid and vbillid and claimid is called an encounter ..Every encounter is coded (meaning prcodes are entered for the encounter)..Createcode is the datetime when the prcode was created and the voided is the time when the prcode was voided... Now I want to deselect rows where the prcode is voided for an encounter but the same prcode for the same encounter has a greater (later)createcode date than the voided date for the same prcode for the same encounter... Please find below the input and the output DDL..

input

create table ##input
(ctextid int,
vbillid int,
claimid int,
createcode datetime2,
prcode varchar(40),
voided datetime2
)

insert into ##input values
('561','622','655','9/20/2021 7:55:26 AM','1190F',''),
('561','622','655','9/15/2021 12:56:42 PM','1190F','9/20/2021 7:55:26 AM'),
('561','622','655','9/20/2021 7:55:36 AM','1190F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('781','3412','8921','9/22/2021 7:55:36 AM','8892F',''),
('781','3412','8921','9/22/2021 7:57:36 AM','8892F',''),
('781','3412','8921','9/22/2021 7:59:36 AM','8892F','9/25/2021 7:59:36 AM')

output

create table ##output
(ctextid int,
vbillid int,
claimid int,
createcode datetime2,
prcode varchar(40),
voided datetime2
)

insert into ##output values
('561','622','655','9/20/2021 7:55:26 AM','1190F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('661','1122','711','9/21/2021 7:55:36 AM','7821F',''),
('781','3412','8921','9/22/2021 7:55:36 AM','8892F',''),
('781','3412','8921','9/22/2021 7:57:36 AM','8892F',''),
('781','3412','8921','9/22/2021 7:59:36 AM','8892F','9/25/2021 7:59:36 AM'),

Thanks,
Arun

Answer 1

Now I want to deselect rows where the prcode is voided for an encounter but the same prcode for the same encounter has a greater (later)createcode date than the voided date for the same prcode for the same encounter...

Can you explain the rules in more detail?

Maybe this is what you want:

;with cte
as(select *,max(voided) over(partition by ctextid,vbillid,claimid) mm
from ##input)

select * from cte
where createcode>=mm

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.