We help IT Professionals succeed at work.
Get Started

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

163 Views
Last Modified: 2018-03-13
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

Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 32 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE