Finding the latest Blocked Status of the patient based on when the assignment for billing is made

Transact-SQL
1

Hi All,

Hope you are doing well!..I am trying to find the latest blocked status for a patient based on the assigndate in the vreport table...A patient visit is indicated by the combination of ctextid and vbillid and patientid indicates the unique ID of that patient... The assign date is the date when that visit was sent for billing...Now in comparison to the assign date I need to find to the blocked status of the patient ..Following are the rules for the same

  1. Using the assigndate for a visit first look at the contextid and the patientid (sourceid in caud table) and pick up the data from oldvalue field where the created (date) is greater than the assign date (1st created date that is greater than the assign date)

  2. If all the created (date) in caud table is less than the assign date then pick up the value from blockyn field in table "client" and the corresponding created date

(id in client table indicates patient id)

Can you please help me here!...Please find the DDL for the input and output tables

vreport table

create table ##vreport
(ctext int,
vbillid int,
patientid int,
assigndate date,
compdate date
)

insert into ##vreport values
('1231','231','12','04/03/2020','04/07/2020'),
('2311','342','19','02/21/2020','')

client table

create table ##client
(c_textid int,
id int ,
created date,
blockyn varchar(20)
)

insert into ##client values
('1231','12','04/01/2020','Y'),
('2311','19','03/28/2020','N'),

caud table

create table ##caud
(c_textid int,
sourceid int,
fieldname varchar(20),
created date,
oldvalue varchar(20)
)

insert into ##caud values
('1231','12','blockyn','03/21/2020','P'),
('1231','12','blockyn','03/15/2020','N'),
('1231','12','blockyn','03/02/2020','Y'),
('2311','19','blockyn','02/24/2020','N'),
('2311','19','blockyn','02/01/2020','P'),
('2311','19','blockyn','03/15/2020','Y')

output table

create table ##output
(ctextid int,
vbillid int,
patientid int,
assigndate date,
compdate date,
created date,
blockyn varchar(20)
)

insert into ##output values
('1231','231','12','04/03/2020','04/07/2020','04/01/2020','Y'),
('2311','342','19','02/21/2020','','02/24/2020','N')

Thanks,
Arun

2 
SELECT 
    COALESCE(ca.c_textid, vr.ctext) AS ctext,
    vr.vbillid,
    vr.patientid,
    vr.assigndate,
    vr.compdate,
    COALESCE(ca.created, cl.created) AS created,
    COALESCE(ca.oldvalue, cl.blockyn) AS blockyn
FROM ##vreport vr
OUTER APPLY (
    SELECT TOP (1) ca.*
    FROM ##caud ca
    WHERE ca.sourceid = vr.patientid AND
        ca.created > vr.assigndate
    ORDER BY ca.created 
) AS ca
LEFT OUTER JOIN ##client cl ON ca. sourceid IS NULL AND
    cl.id = vr.patientid
ORDER BY vr.patientid
1 Like