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.