Results 1 to 7 of 7

Thread: how to join tables and get desired data...

  1. #1
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139

    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!!

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Before I read it. COuld you please provide sample data and the desired result?

  3. #3
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  5. #5
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139
    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."

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  7. #7
    Join Date
    Oct 2002
    Location
    queens
    Posts
    139
    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
  •