Avatar of biotec
biotec

asked on 

Two SQL queries that need to be incorporated into one (possible Union)

One of these queries pulls internal labs and the other external labs. There are duplicate patients that need to be removed when joining these. Also wondering if instead of using multiple like statements I can use IN when it contains wildcards?

Internal Labs:
SELECT  * from (select o2.acttext, o2.obsvalue, p.person_nbr, p.last_name, p.first_name, pe.enc_id, pe.person_id,
ROW_NUMBER() OVER (PARTITION BY p.person_nbr ORDER BY o2.completeddate DESC)rowno, o2.completeddate
FROM order_ o2	
	  inner join person p with(nolock) on o2.person_id = p.person_id 
	  INNER JOIN patient_encounter AS pe with(nolock)  ON p.person_id = pe.person_id
	  inner join patient_diagnosis pd with(nolock) on pe.enc_id = pd.enc_id
	  INNER JOIN appointments AS a	 with(nolock)  ON pe.enc_id	= a.enc_id
	  WHERE o2.obsvalue IS NOT NULL 
     and (o2.completeddate >= '20150101'
                  AND o2.completeddate < '20160101')
	 and o2.acttext in ('GLYCATED HEMOGLOBIN TEST', 'Hemoglobin A1c')
	  and (pd.diagnosis_code_id  like '250%' or pd.diagnosis_code_id like '648.0%' 
	or pd.diagnosis_code_id  like '357.2' or pd.diagnosis_code_id like '362.0[0-7]' or pd.diagnosis_code_id  like '366.41' or pd.diagnosis_code_id like 'E1[0-3].%' 
	or pd.diagnosis_code_id  like 'O24.[0-4]0%' or pd.diagnosis_code_id like 'O24.[4-9][2-9]') 
	 and pe.person_id in (select pe.person_id from patient_encounter pe 
	INNER JOIN appointments AS a	 with(nolock)  ON pe.enc_id	= a.enc_id
	where  (pe.enc_timestamp >= '20151001'
                  AND pe.enc_timestamp < '20160101'
				  and 	a.appt_kept_ind = 'Y' and a.resched_ind = 'N'))) inofficea1c
	where rowno = 1

Open in new window

External Labs:
	SELECT  * from (select p.last_name as LastName, p.first_name as FirstName, p.sex as Sex, p.language as Language, p.Ethnicity as Ethnicity, 
p.Race as Race, p.date_of_birth as DOB, res.obs_date_time as A1cDate, res.result_desc as ResultDescription, res.observ_value as A1cResultValue, res.obs_id as OBXObsID,
ROW_NUMBER() OVER (PARTITION BY p.person_nbr ORDER BY  res.obs_date_time DESC)rowno
FROM lab_results_obx res	  
	  inner join person p with(nolock) on res.person_id = p.person_id 
	  INNER JOIN patient_encounter AS pe with(nolock)  ON p.person_id = pe.person_id
	  inner join patient_diagnosis pd with(nolock) on pe.enc_id = pd.enc_id
	  INNER JOIN appointments AS a	 with(nolock)  ON pe.enc_id	= a.enc_id
	  WHERE res.observ_value IS NOT NULL 
     and (res.obs_date_time >= '20150101'
                  AND res.obs_date_time < '20160101')
	 and (res.obs_id like '%HEMOGLOBIN A1C%'  or res.obs_id like '%HBG. A1C%' or res.obs_id like '%HBG.AIC%' or res.obs_id like '%HEMOGLOBIN A1c%')
	  and (pd.diagnosis_code_id  like '250%' or pd.diagnosis_code_id like '648.0%' 
	or pd.diagnosis_code_id  like '357.2' or pd.diagnosis_code_id like '362.0[0-7]' or pd.diagnosis_code_id  like '366.41' or pd.diagnosis_code_id like 'E1[0-3].%' 
	or pd.diagnosis_code_id  like 'O24.[0-4]0%' or pd.diagnosis_code_id like 'O24.[4-9][2-9]') 
	 and pe.person_id in (select pe.person_id from patient_encounter pe 
	INNER JOIN appointments AS a	 with(nolock)  ON pe.enc_id	= a.enc_id
	where  (pe.enc_timestamp >= '20151001'
                  AND pe.enc_timestamp < '20160101'
				  and 	a.appt_kept_ind = 'Y' and a.resched_ind = 'N'))) outsidelabA1c
	where rowno = 1

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
biotec

8/22/2022 - Mon