CODEWITHSUNDEEP
sqlsql-servert-sql
michael95677
michael95677

Reputation: 21

SQL query to find count of visits within date range

The query below works in just about all aspects except it returns the total count of encounters (pe.enc_id) ever. I'm trying to find the total count of visits just in 2016. 

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

Upvotes: 0

Views: 596

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your query sure looks like it is filtering on the date. I do have some suggestions:

  • You have no need for select distinct
  • Your subquery is overly complicated.
  • In fact, I don't think it is necessary.

You appear to be looking for patients that have a particular diagnosis, and then counting the number of diagnoses. You can do this all in the outer aggregation, by extending the having clause:

So:

select 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') 
group by p.person_nbr, p.last_name, p.first_name, p.race
having count(pe.enc_id) >= 2 and
       sum(case when 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]' then 1 else 0 end) > 0;

Upvotes: 2

Related Questions