Error - in Where clause 1

[mchambers]

Hello,

I am trying to modify a script where a patient has greater than two episodes of care and I am receiving an error. I've tried changing the script however SQL throws back an error each time.
Error - An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
I greatly appreciate some help with this.

SELECT
      (vesd.PATID) as "Patient ID"         
      
FROM discharge vesd
 
INNER JOIN episode_history eh    
       ON vesd.PATID = eh.PATID
       AND vesd.EPISODE_NUMBER <> eh.EPISODE_NUMBER
       AND vesd.FACILITY = eh.FACILITY
       AND eh.preadmit_admission_date > vesd.date_of_discharge
      
--Adds client name & last name
INNER JOIN demographics pcd
       ON vesd.PATID = pcd.PATID
       AND vesd.FACILITY = pcd.FACILITY
      
--Adds guarantor name & code
INNER JOIN guarantor  bgd
       ON vesd.PATID = bgd.PATID
       AND vesd.EPISODE_NUMBER = bgd.EPISODE_NUMBER
       AND vesd.FACILITY = bgd.FACILITY
 
       WHERE DATEDIFF(d, vesd.date_of_discharge, eh.preadmit_admission_date) <= 180   
    --Dates for 180 days back from end of week being measured
    AND vesd.date_of_discharge BETWEEN '2019-04-20' AND '2019-11-17'
       AND eh.preadmit_admission_date BETWEEN '2019-11-11' AND '2019-11-17'
       --Admission Program       
       AND vesd.program_code IN ('2410','2430','2450','4570','9000')
       --Readmission Program (Discharge)
       AND eh.program_code IN ('2410','2430','2450','4570')
       AND bgd.guarantor_order = 1
       --guarantors
       AND bgd.guarantor_id IN ('2000','2004','2005','2006','2007','2008','2009','2016','2050')
      AND COUNT(eh.episode_number) > 2 --(Getting error when I add epsiode count)
GROUP BY vesd.PATID

Thanks

 

[Andrzejek]

Did you try:

SELECT (vesd.PATID) as "Patient ID", [blue]COUNT(eh.episode_number) AS XYZ[/blue],
...
WHERE
...[blue]
HAVING COUNT(eh.episode_number) > 2[/blue]
...

---- Andy

There is a great need for a sarcasm font.

 

[mchambers]

That worked! Thanks!