-
how to join tables and get desired data...
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
Thanks in advance!!
-
Before I read it. COuld you please provide sample data and the desired result?
-
Patient_tran table:
patient_id entry_date
abc 01/01/04
bcd 02/05/05
xyz 04/07/05
patient_plan table:
patient_id plan_id eff_dt term_dt
abc 123 02/28/04 06/30/05
abc 234 09/20/03 01/31/04
abc 345 01/01/03 01/31/03
bcd 444 02/31/04 null
xyz 555 03/30/02 01/01/03
xyz 666 02/01/03 12/31/04
the result should be:
patient_id plan_id
abc 234
bcd 444
xyz 666
Thanks again.
-
Here is the raw solution. There is enough room to optimize this query. Please feel free to update the query.
use tempdb
go
create table Patient_tran
(patient_id varchar(10), entry_date datetime)
insert into Patient_tran select 'abc', '01/01/04'
insert into Patient_tran select 'bcd', '02/05/05'
insert into Patient_tran select 'xyz', '04/07/05'
go
create table patient_plan
(patient_id varchar(3),plan_id int, eff_dt datetime,
term_dt datetime)
go
insert into patient_plan select 'abc', '123', '02/28/04', '06/30/05'
insert into patient_plan select 'abc', '234', '09/20/03', '01/31/04'
insert into patient_plan select 'abc', '345', '01/01/03', '01/31/03'
insert into patient_plan select 'bcd', '444', '02/28/04', null
insert into patient_plan select 'xyz', '555', '03/30/02', '01/01/03'
insert into patient_plan select 'xyz', '666', '02/01/03', '12/31/04'
go
select patient_id, PlanID=case
when plan_id is not null then plan_id
when plan_id is null and plan_id2 is not null then plan_id2
when plan_id is null and plan_id2 is null then plan_id3 end
from
(
select a.patient_id,(select b.plan_id from patient_plan b where a.patient_id=b.patient_id
and a.entry_date between b.eff_dt and b.term_dt) as plan_id,
(select b.plan_id from patient_plan b where a.patient_id=b.patient_id
and b.term_dt is NULL) as plan_id2,
(select b.plan_id from patient_plan b where a.patient_id=b.patient_id
and b.term_dt = (select max(c.term_dt) from patient_plan c where c.patient_id=b.patient_id)) as plan_id3
from Patient_tran a
) as mytable
go
-
It gives me the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
-
select patient_id, PlanID=case
when plan_id is not null then plan_id
when plan_id is null and plan_id2 is not null then plan_id2
when plan_id is null and plan_id2 is null then plan_id3 end
from
(
select a.patient_id,(select top 1 b.plan_id from patient_plan b where a.patient_id=b.patient_id
and a.entry_date between b.eff_dt and b.term_dt) as plan_id,
(select top 1 b.plan_id from patient_plan b where a.patient_id=b.patient_id
and b.term_dt is NULL) as plan_id2,
(select top 1 b.plan_id from patient_plan b where a.patient_id=b.patient_id
and b.term_dt = (select max(c.term_dt) from patient_plan c where c.patient_id=b.patient_id)) as plan_id3
from Patient_tran a
) as mytable
go
-
Thanks a lot, that worked.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|