|
sross81
Posting Yak Master
228 Posts |
 Posted - 2009-05-26 : 17:55:27
|
| Hello,I have a query that has two derived tables in it and it works fine when I just have the one derived table and it actually works okay with the second but it seems like the data isn't lining up right so I was thinking maybe I need to join the derived tables together in some way. Should I join these tables on the person_id key? Here is what I have:select p.first_name,p.last_name,cast(p.date_of_birth as datetime) as PtDOB,cast(MAX(CASE WHEN c.appt_date <= getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateoflastvisit,cast(MIN(CASE WHEN c.appt_date > getdate() THEN c.appt_date ELSE NULL END) as datetime) as dateofnextvisit,t.dt_comp_lab_cbc as LastCBCDate, t.txt_lab_cbc_st as LastCBCStatus,t.dt_comp_lab_cd4 as LastCD4Date,t.txt_lab_cd4_st as LastCD4Status,t.dt_comp_lab_cd4p as LastCD4PDate, t.txt_lab_cd4_percent_st as LastCD4PStatus,t.dt_comp_lab_cmp as LastCMPDate,t.txt_lab_cmp_st as LastCMPStatus,t.dt_comp_lab_creatinine as LastCreatinineDate, t.txt_lab_creatinine_st as LastCreatinineStatus,t.dt_comp_lab_g6pd as LastHSVIIABDate,t.txt_lab_g6pd_st as LastHSVIIABDate,t.dt_comp_lab_gfr as LastGFRDate, t.txt_lab_gfr_st as LastGFRStatus,t.dt_comp_lab_hepa_ab as LastHepAabDate, t.txt_lab_hepa_ab_st as LastHepAabStatus,t.dt_comp_lab_ppd as LastPPDDate, t.txt_lab_ppd_st as LastPPDStatus,t.dt_comp_lab_hepbcab as LastHepBcAbDate,t.txt_lab_hepb_cad_st as LastHepBcAbStatus,t.dt_comp_lab_hepbsab as LastHepBsAbDate,t.txt_lab_hepb_sab_st as LastHepBsAbStatus,t.dt_comp_lab_hepbsag as LastHepBsAgDate,t.txt_lab_hepB_sag_st as LastHepBsAgStatus,t.dt_comp_lab_hepc_ab as LastHepCDate,t.txt_lab_hepc_ab_st as LastHepCStatus,t.dt_comp_lab_hiv_rna as LastHIVRnaDate,t.txt_lab_hiv_rna_st as LastHIVRnaStatus,t.dt_comp_lab_toxoplasm as LastToxoplasmDate,t.txt_lab_toxoplasmosis_st as LastToxoplasmStatus,t.dt_comp_lab_ua as LastUADate,t.txt_lab_ua_st as LastUAStatus,t.dt_comp_lab_varicella as LastVaricellaDate,t.txt_lab_varicella_st as LastVaricellaStatus,t.dt_comp_lab_gc as LastGCDate,t.txt_lab_gc_st as LastGCStatus,t.dt_comp_lab_rpr as LastRPRDate,t.txt_lab_rpr_st as LastRPRStatus,t2.last_pap as LastPapDate, t.txt_lab_pap_st as LastPapStatusfrom person pinner join (select h.* from hiv_flowsheet_ h inner join (select person_id, max(create_timestamp) as LastHIVFlowEntry from hiv_flowsheet_ group by person_id) h1 on h1.person_id=h.person_id and h1.LastHIVFlowEntry=h.create_timestamp )ton t.person_id=p.person_idinner join (select hm.* from health_maint_ hm inner join (select person_id, max(create_timestamp) as LastHMEntry from health_maint_ group by person_id) h2 on h2.person_id =hm.person_id and h2.LastHMEntry=hm.create_timestamp)t2on t2.person_id=p.person_id join appointments c on t.person_id = c.person_idjoin provider_mstr d on c.rendering_provider_id = d.provider_id/*join health_maint_ e on t.person_id = e.person_id and t.enc_id =e.enc_id*//*join disease_mngt_ f on t.person_id = f.person_id and t.enc_id = f.enc_id*/where d.description like '%applin%' group by p.first_name,p.last_name,cast(p.date_of_birth as datetime),t.dt_comp_lab_cbc,t.txt_lab_cbc_st,t.dt_comp_lab_CD4,t.txt_lab_CD4_st,t.dt_comp_lab_CD4P,t.txt_lab_CD4_percent_st,t.dt_comp_lab_cmp,t.txt_lab_cmp_st,t.dt_comp_lab_creatinine,t.txt_lab_creatinine_st,t.dt_comp_lab_G6PD,t.txt_lab_G6PD_st,t.dt_comp_lab_gfr,t.txt_lab_gfr_st,t.dt_comp_lab_hepa_ab, t.txt_lab_hepa_ab_st,t.dt_comp_lab_ppd, t.txt_lab_ppd_st,t.dt_comp_lab_hepbcab,t.txt_lab_hepb_cad_st,t.dt_comp_lab_hepbsab,t.txt_lab_hepb_sab_st,t.dt_comp_lab_hepbsag,t.txt_lab_hepB_sag_st,t.dt_comp_lab_hepc_ab,t.txt_lab_hepc_ab_st,t.dt_comp_lab_hiv_rna,t.txt_lab_hiv_rna_st,t.dt_comp_lab_toxoplasm,t.txt_lab_toxoplasmosis_st,t.dt_comp_lab_ua,t.txt_lab_ua_st,t.dt_comp_lab_varicella,t.txt_lab_varicella_st,t.dt_comp_lab_gc,t.txt_lab_gc_st,t.dt_comp_lab_rpr,t.txt_lab_rpr_st,t2.last_pap,t.txt_lab_pap_stThanks in Advance!Sherri |
|
Joining Derived Tables