asked on
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