Avatar of biotec
biotec

asked on 

Simple SQL query need to filter by count greater than 2 within time frame

Problem with this query below is it gives number of visits/encounters (pe.enc_id) ever and I need the count within the current year (20160101 - 20161231).

select distinct p.person_nbr, p.last_name, p.first_name, p.race, count(pe.enc_id) as count_of_encounters 
from patient_encounter pe
	inner join person p on pe.person_id = p.person_id
	inner join patient_diagnosis pd on pd.enc_id = pe.enc_id
WHERE (p.date_of_birth between '19420101' and '19981231' and pe.enc_timestamp >= '20160101' AND pe.enc_timestamp  < '20170101')
	and pd.person_id in (select distinct p.person_id from person p  inner join patient_diagnosis pd on p.person_id = pd.person_id
where (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]') )
group by p.person_nbr, p.last_name, p.first_name, p.race
having count(pe.enc_id) >= 2

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon