question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked EchoLiu-msft commented

Delete records which have been updated post the voidance

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-generalsql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I got an email notification that @Viorel-1 responded to this thread...But I am not able to see the response here...

0 Votes 0 ·
EchoLiu-msft avatar image EchoLiu-msft ArunChandramouli-6978 ·

I saw @Viorel-1's answer deleted.

0 Votes 0 ·

Do you have any update?

0 Votes 0 ·

@EchoLiu-msft : Please accept my apologies for my late response...I got the response from @Viorel-1 with the following code and that worked well..Thanks @Viorel-1 ..Really appreciate your help!...@EchoLiu-msft : Thanks for your help too!



Fix the input data and try these explicit conditions: select from ##input i1 where not exists ( select from ##input i2 where i2.ctextid = i1.ctextid and i2.vbillid = i1.vbillid and i2.prcode = i1.prcode and i2.createcode >= i1.createcode and i2.voided <> '' and exists ( select * from ##input i3 where i3.ctextid = i2.ctextid and i3.vbillid = i2.vbillid and i3.prcode = i2.prcode and i3.createcode > i2.createcode and i3.voided = '' ) ) order by ctextid, vbillid, claimid, createcode

0 Votes 0 ·
EchoLiu-msft avatar image EchoLiu-msft ArunChandramouli-6978 ·

Thank you for the update!Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.