Having clause being ignored

Question

In this query, I am attempting to get a count that gives me a count of patients for each practice under given conditions.

The issue is that I have to show patients who have had >=3 office visits in the past year.

Count(D.PID)

in the select list is ignoring

HAVING count(admitdatetime)>=3

Here is my query

select distinct D.PracticeAbbrevName, D.ProviderLastName, count(D.pid) AS Count

from PersonDetail AS D  
left join Visit AS V on D.PID = V.PID

where D.A1C >=7.5  and V.admitdatetime >= (getdate()-365) and D.A1CDays <180 and D.Diabetes = 1 

group by D.PracticeAbbrevName, D.ProviderLastName

having count(admitdatetime)>=3

order by PracticeAbbrevName

If I get rid of the count function for D.pid, and just display each PID individually, my having phrase works properly.

There is something about count and having that do now work properly together.

You cannot display each PID individually with such `group by`. Please show your result and why it is not correct. — GSerg, Nov 05 '15 at 21:29
NOTE: `COUNT(column)` is not the same as `COUNT(DISTINCT column)`. (Also using `SELECT DISTINCT` with `GROUP BY` is almost always redundant, but not what's causing your problem here) — RBarryYoung, Nov 05 '15 at 21:32
when I display each PID individually, I add PID to group by. As an example, for one providerlastname, COUNT is 21, but when I select by individual PID, there are 2. When I then remove the having statement for individual PID, it goes to 21 unique PIDs — John S, Nov 05 '15 at 21:33
Thank you @RBarryYoung I actually had count(distinct d.pid) but in trying to play around and find the solution I moved it to where it is shown above. That does not fix the problem as you said — John S, Nov 05 '15 at 21:34

JohnS · Answer 1 · 2015-11-05T21:43:56.103

0

Revised answer:

SELECT DISTINCT
  D.PracticeAbbrevName,
  D.ProviderLastName,
  COUNT(D.pid) AS PIDCount,
  COUNT(admitdatetime) AS AdmitCount
FROM
  PersonDetail AS D
  LEFT JOIN Visit AS V
    ON D.PID = V.PID
WHERE
  D.A1C >= 7.5
  AND V.admitdatetime >= ( GETDATE() - 365 )
  AND D.A1CDays < 180
  AND D.Diabetes = 1
GROUP BY
  D.PracticeAbbrevName,
  D.ProviderLastName
HAVING
  COUNT(admitdatetime) >= 3
ORDER BY
  PracticeAbbrevName

edited Nov 05 '15 at 21:43

answered Nov 05 '15 at 21:19

JohnS

1,942
1
13
16

3

I'm pretty certain `having` may or may not match `select`, and I have queries where it does not. – GSerg Nov 05 '15 at 21:27
neither of those is correct. I want to select the count of PID but I need to have a way to show the count of PID where admitdatetime >=3 in the past 365 days. – John S Nov 05 '15 at 21:27

score 0 · Answer 2 · answered Nov 06 '15 at 02:46

You're trying to do too much at once. Split the logic in 2 steps:

Query grouping by PID to filter out patients that don't meet your criteria.
Query grouping by practice to get a patient count.

Your query would look like this:

;with EligiblePatients as (
    select d.pid,
           d.PracticeAbbrevName,
           d.ProviderLastName
      from PersonDetail d
      left join Visit v
        on v.pid = d.pid
       and v.admitdatetime >= (getdate()-365)
     where d.A1C >= 7.5
       and d.A1CDays < 180
       and d.Diabetes = 1
     group by d.pid,
              d.PracticeAbbrevName,
              d.ProviderLastName
     having count(v.pid) >= 3
)
select PracticeAbbrevName,
       ProviderLastName,
       COUNT(*) as PatientCount
  from EligiblePatients
 group by PracticeAbbrevName,
          ProviderLastName
 order by PracticeAbbrevName

Having clause being ignored

2 Answers2