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

Question

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

Answer 1

Hi @Hellothere8028 ,

Sorry that I made a small mistake on the join part 'a.ctext=a.ctext ' and I just realized that!

Please refer below query:

select a.ctext,a.vbillid,a.patientid,a.assigndate,IIF(compdate='1900-01-01',NULL,compdate) compdate  
,case when a.assigndate> max then b.created else c.created end created  
,case when a.assigndate> max then b.blockyn else c.oldvalue end  blockyn  
from ##vreport a  
left join ##client b on a.ctext=b.c_textid and a.patientid=b.id  
left join (select ROW_NUMBER() over (partition by c_textid,sourceid order by (select null)) rn, * from ##caud where fieldname ='blockyn') c on a.ctext=c.c_textid and c.sourceid=a.patientid  
left join (select c_textid,sourceid,max(created) max from ##caud group by c_textid,sourceid) d on d.c_textid=c.c_textid and d.sourceid=c.sourceid  
where c.rn=1  

Output:

ctext	vbillid	patientid	assigndate	compdate	created	blockyn  
1231	231	12	2020-04-03	2020-04-07	2020-04-01	Y  
2311	342	19	2020-02-21	NULL	2020-02-24	N  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Hellothere8028 ,

Thank you so much for posting here in Microsoft Q&A.

Please refer below and check whether it is working. If not, please provide more sample data and expected output.

;with cte as   
(select ROW_NUMBER() over (partition by c_textid,sourceid order by (select null)) rn, * from ##caud)  
,max as (  
select c_textid,sourceid,max(created) max  
from ##caud   
group by c_textid,sourceid)  
select a.ctext,a.vbillid,a.patientid,a.assigndate,IIF(compdate='1900-01-01',NULL,compdate) compdate  
,case when a.assigndate> max then b.created else c.created end created  
,case when a.assigndate> max then b.blockyn else c.oldvalue end  blockyn  
from ##vreport a  
 left join ##client b on a.ctext=b.c_textid and a.patientid=b.id  
left join cte c on a.ctext=a.ctext and c.sourceid=a.patientid  
left join max d on d.c_textid=c.c_textid and d.sourceid=c.sourceid  
where c.rn=1  

Output:

ctext	vbillid	patientid	assigndate	compdate	created	blockyn  
1231	231	12	2020-04-03	2020-04-07	2020-04-01	Y  
2311	342	19	2020-02-21	NULL	2020-02-24	N  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Hellothere8028 ,

Thanks for your update.

Above query is equal to below:

 select a.ctext,a.vbillid,a.patientid,a.assigndate,IIF(compdate='1900-01-01',NULL,compdate) compdate  
 ,case when a.assigndate> max then b.created else c.created end created  
 ,case when a.assigndate> max then b.blockyn else c.oldvalue end  blockyn  
 from ##vreport a  
 left join ##client b on a.ctext=b.c_textid and a.patientid=b.id  
 left join (select ROW_NUMBER() over (partition by c_textid,sourceid order by (select null)) rn, * from ##caud) c on a.ctext=a.ctext and c.sourceid=a.patientid  
 left join (select c_textid,sourceid,max(created) max from ##caud group by c_textid,sourceid) d on d.c_textid=c.c_textid and d.sourceid=c.sourceid  
 where c.rn=1  

I added ROW_NUMBER() and get the first record using rn=1 since you mentioned '1st created date that is greater than the assign date'.

left join cte c on a.ctext=a.ctext and c.sourceid=a.patientid

This is similar to below:

  select ...  
  from ##vreport a  
  left join ##caud c on a.ctext=a.ctext and c.sourceid=a.patientid  

If my query is not working, please provide more sample data and expected output.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.