question

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

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

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




sql-server-transact-sql
· 2
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.

Hi @ArunChandramouli-6978,

Could you please provide any update?

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

Please 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. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

@MelissaMa-msft : My apologies for the late response!...I am still working through the query...Will update this thread very soon..

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 2
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.

@MelissaMa-msft : Appreciate your response! The fieldname ='blockyn' would also come for the last left join

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 where fieldname='blockyn' group by c_textid,sourceid) d on d.c_textid=c.c_textid and d.sourceid=c.sourceid
where c.rn=1

0 Votes 0 ·

@MelissaMa-msft : Thank you so much for your help!

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 1
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.

Hi Melissa, Really appreciate your response!...Just wanted to check this line of code with you..

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



Thanks,
Arun

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 1
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.

Hi @MelissaMa-msft : I have few questions on the above Query:

I have made a correction with the following statement

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

to

left join (select ROW_NUMBER() over (partition by c_textid,sourceid order by (select null)) rn, * from ##caud) c on a.ctext=c. c_textid and c.sourceid=a.patientid


and also I want to include the filter fieldname =blockyn in the table ##caud ..Can you please help me here on how the code would modify with regard to the same ..Appreciate your help!..



0 Votes 0 ·