troubleshooting Question

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

Avatar of biotec
biotec asked on
Microsoft SQL ServerSQL
32 Comments1 Solution164 ViewsLast Modified:
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
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 32 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 32 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros