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

biotecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surendra NathTechnology LeadCommented:
The query looks good, but check the enc_timestamp it may have stamped this year for alll the rows returnd
biotecAuthor Commented:
The timestamps are correct. There is nothing in the query that restricts the count of enc_id to this year and that is what is missing.
Scott PletcherSenior DBACommented:
You're not limiting the diagnosis to only 2016.  I'm not sure what the column name is for the date in the pd table, but something like below may give you what you need:


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
WHERE (p.date_of_birth between '19420101' and '19981231'
    and pe.enc_timestamp >= '20160101' AND pe.enc_timestamp  < '20170101')
      and pe.person_id in (
          select distinct person_id
          from patient_diagnosis
        where
           date_column >= '20160101' --??column name??
           and date_column < '20170101'
--??column name??
           and (diagnosis_code_id  like '250%' or diagnosis_code_id like '648.0%'
               or diagnosis_code_id  like '357.2' or diagnosis_code_id like '362.0[0-7]'
               or diagnosis_code_id  like '366.41' or diagnosis_code_id like 'E1[0-3].%'
               or diagnosis_code_id  like 'O24.[0-4]0%' or 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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

biotecAuthor Commented:
The diagnosis can happen ever. The problem is the count(pe.enc_id) as count_of_encounters. It looks for any encounters ever, or so it seems and the I'm not sure the last having statement of >=2 is only looking at encounters for this year.
Vadim RappCommented:
It looks like you already have it:

and pe.enc_timestamp >= '20160101' AND pe.enc_timestamp  < '20170101'
James0628Commented:
Are you sure that the count is including all dates?  Or is it just that the count seems high, and you're assuming that it's because it includes all dates?

 If the counts are high, maybe they're being inflated by something else.  For example, the main query has a Join with patient_diagnosis, but you don't include any columns from pd in the Select or Group By.  Unless I'm missing something, if a patient has more than one entry in patient_diagnosis, the patient_encounter rows will be repeated for each entry in patient_diagnosis, and those "extra" rows will be included in the counts.  If that's a problem, you could add something like pd.person_id to the Group By.

 On a side note, it doesn't seem like you really need to include the person table in the sub-query.  Why not just use
(select distinct pd.person_id from patient_diagnosis pd
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]') )

Open in new window


James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
biotecAuthor Commented:
James,
This is why the monthly fee for this site is so worth it. I just did not look at the fact that the inner join to patient_diagnosis at the beginning could cause the problems. I assume things about the data and the visits in this case and just thought the logic was good. It wasn't! You helped me spot two areas where there were problems although the join in the sub-query was not the issue it wasn't done correctly and just caused extra work for the query.
Thanks!!!!!
James0628Commented:
You're welcome.  Glad I could help.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.