Delete records which have been updated post the voidance

Hellothere8028 821 Reputation points
2021-10-18T12:36:07.987+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,779 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-10-19T03:40:44.91+00:00

    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.

    0 comments