Thanks for pointing this out and that fixed the error.
Now the problem is that I need add more coulmns for example employee name, dept, flu date etc along with % compliance.
Any insight on how to do this?
****************************
SELECT
E.FLDSUPRNAME AS Supervisor,
E.FLDLNAME, E.FLDFNAME,E.FLDID, E.FLDDEPT,
COUNT(E.FLDID) AS CNT,
1.0*SUM(CASE WHEN I.FLDDATE IS NULL THEN 0 ELSE 1 END)/COUNT(E.FLDID) AS Percent_Flu_Compliant,
1.0*SUM(CASE WHEN F.FLDDATE IS NULL OR (F.FLDDATE+365) < GETDATE() THEN 0 ELSE 1 END)/
COUNT(E.FLDID) AS Percent_Fit_Compliant,
1.0*COUNT(CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL
AND CDUEx.FLDDATEDUE IS NULL THEN 1 ELSE 0 END)/COUNT(E.FLDID) AS Percent_TB_Compliant
FROM EMPLOYEE E
LEFT OUTER JOIN DEPT D
ON D.FLDCODE= E.FLDDEPT
LEFT OUTER JOIN IMMUNE I
ON I.FLDEMPLOYEE = E.FLDREC_NUM
AND I.FLDTYPE IN ('109', '111')
AND I.FLDDATE = (
SELECT MAX(FLDDATE)
FROM IMMUNE I2
WHERE E.FLDREC_NUM = I2.FLDEMPLOYEE AND I2.FLDTYPE IN ('109','111')
)
AND I.FLDDATE >= @Flu_Date
AND I.FLDDATE <= GETDATE()
LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
AND F.FLDDATE = (SELECT MAX(FLDDATE) FROM FITTEST F2 WHERE E.FLDREC_NUM = F2.FLDEMPLOYEE)
LEFT OUTER JOIN REQEXAM PPDx
ON PPDx.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDx.FLDPHYSICAL = '110' AND
PPDx.FLDDATEDUE <= getdate()
LEFT OUTER JOIN REQEXAM PPDL
ON PPDL.FLDEMPLOYEE = E.FLDREC_NUM
AND PPDL.FLDPHYSICAL = '110'
LEFT OUTER JOIN REQEXAM TBSSx
ON TBSSx.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSx.FLDPHYSICAL = 'TBSS' AND
TBSSx.FLDDATEDUE <= getdate()
LEFT OUTER JOIN REQEXAM TBSSL
ON TBSSL.FLDEMPLOYEE = E.FLDREC_NUM
AND TBSSL.FLDPHYSICAL = 'TBSS'
LEFT OUTER JOIN REQEXAM CDUEx
ON CDUEx.FLDEMPLOYEE = E.FLDREC_NUM
AND CDUEx.FLDPHYSICAL = '109' AND
CDUEx.FLDDATEDUE <= getdate()
WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
AND E.FLDSUPRNAME <> ' '
AND E.FLDID <> ' '
GROUP BY
E.FLDSUPRNAME,E.FLDLNAME, E.FLDFNAME,E.FLDID, E.FLDDEPT
ORDER BY
E.FLDSUPRNAME