February 26, 2013 at 11:18 am
Hello,
I'm trying to create a report for listing the % compliance by Supervisor along with employee name, id, dept, and dates.
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
E.FLDDEPT,
D.FLDDESCR AS DEPT,
E.FLDJOB,
J.FLDDESCR,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
TBLAST = CONVERT(VARCHAR(10),PPDL.FLDDATELAST,101),
TB = CONVERT(VARCHAR(10),PPDx.FLDDATEDUE,101),
TBSS = CONVERT(VARCHAR(10),TBSSx.FLDDATEDUE, 101),
TBSL = CONVERT(VARCHAR(10),TBSSL.FLDDATELAST, 101),
CXRAY = CONVERT(VARCHAR(10),CDUEx.FLDDATEDUE, 101),
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
E.FLDSUPRNAME,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Compliant,
CASE WHEN F.FLDDATE IS NULL OR (F.FLDDATE+365) < GETDATE() THEN 'NO' ELSE 'YES' END AS Fit_Compliant,
Tb_Compliant = CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL
AND CDUEx.FLDDATEDUE IS NULL
THEN 'YES'
ELSE 'NO'
END
FROM
EMPLOYEE E
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 I.FLDDATE >= @Flu_Date AND I.FLDDATE <= GETDATE()
LEFT OUTER JOIN JOB J ON
E.FLDJOB = J.FLDCODE
LEFT OUTER JOIN DEPT D ON
E.FLDDEPT = D.FLDCODE
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()
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)
WHERE
E.FLDCOMP = @company AND
E.FLDSTATUS = 'A' AND E.FLDSUPRNAME <> ' ' AND E.FLDID <> ' '
ORDER BY E.FLDSUPRNAME
*******************
I modified the above code to get the % by supervisor.
SELECT
E.FLDSUPRNAME AS Supervisor,
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
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
ORDER BY
E.FLDSUPRNAME
*********************
It worked fine for Flu and Fit test compliance. For TB Compliance I got the error "Operand data type varchar is invalid for sum operator" - 1.0*SUM(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
The problem is that I need to list employee id, name, dept and fludate, fit test date and tb date along with % compliance.
Please let me know if you have any insight on this. Many thanks for your time.
thanks
February 26, 2013 at 2:59 pm
Are you sure the code you pasted is the code that you are trying to execute?
The line of code in your error message doesn't appear in the sample code you posted above: "1.0*SUM(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"
That being said, assuming the error message is accurate the problem is that you've got the 1 and the 0 in your expression wrapped in single quotes - in other words they are varchar data types instead of integers. Remove the single quotes and the error should go away.
February 26, 2013 at 3:25 pm
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
February 27, 2013 at 7:04 am
I'm not entirely sure I follow what the issue is. Wouldn't you just need to add those fields to your Select and Group By?
February 27, 2013 at 7:27 am
I'll second that. All that's required is to add the extra fields you need into your SELECT and GROUP BY clauses, assuming you have JOINed to the relevant tables.
February 27, 2013 at 8:58 am
If you look at the code, I have already added the fields on select and group by statements. But now I'm not getting the %compliance I needed.
For example, it's showing 100% compliance for all supervisors.
AASHEIM, DONNA J.100.00%1000.00% 100.00%
MARTIN JJACOB 8788888
JOEL ABRAHAM 8968598
ADAM, JANICE G. 100.00%1000.00% 100.00%
CYNTIA SUNNY 7898888
AMBURGEY, DAVID A.100.00%100.00%100.00%
GEORGE MANUEL 87969096
JIM PONDER 67488888
SUZANNE MASON 97578588
ANDERSON, PATRICIA100.00%100.00%100.00%
LINDA FoGUS 74848888
CHAR CONNER 89947889
SAM SON 78848888
KATHY MAHER 9899888
When I remove name and id fields from the select statement and group by, then I'm getting the correct result
AASHEIM, DONNA J.100%100%100%
ADAM, JANICE G. 100%100%0%
AMBURGEY, DAVID A.92.7%96.3%88.9%
Let me know
February 27, 2013 at 9:08 am
I think I understand what you're saying. If you are selecting E.FLDID and also using the count(E.FLDID) in your expressions, you're getting one row per E.FLDID, which means each expression is going to evaluate to either 0/1 or 1/1. I don't think you want E.FLDID in your Select list or your Group By.
Unless of course, I'm misunderstanding what you're trying to accomplish.
February 27, 2013 at 9:37 am
Yes you are right. If I remove ID, and name from select and group by it works fine.
But I have to list Employee ID, name along with % percentage compliance by supervisor.
Is there anyway to do this?
February 27, 2013 at 9:41 am
You could try a derived table expression using the WITH construct, something like:
WITH <table> AS
(SELECT ....
)
SELECT
.....
where the fields in your "parent" SELECT needn't be present in your derived table select, if you get my meaning
February 27, 2013 at 9:44 am
Ok, thanks for the insight.
I will try that.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply