Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anishap
Yak Posting Veteran
61 Posts |
 Posted - 2013-02-25 : 13:39:30
|
Hello,
I'm trying to create a report to list the % compliance by supervisor. Here is the code:
SELECT
E.FLDSUPRNAME AS Supervisor,
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,
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 want to count the number of employees per supervisor and their % compliance for Flu_Compliance, Fit_Compliance, and TB_Compliance
If you have any insight on this, please let me know.
many thanks for your time.
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-25 : 14:30:31
|
Do you also need to simultaneously keep all the other columns? Assuming you don't, here is a sample of how you can do it. I am showing only Flu_Compliant - other counts would be similar.SELECT
E.FLDSUPRNAME AS Supervisor,
SUM(1) AS EmployeeCount,
1.0*SUM(CASE WHEN I.FLDDATE IS NULL THEN 0 ELSE 1 END)/
SUM(1) AS Percent_Flu_Compliant
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()
WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
AND E.FLDSUPRNAME <> ' '
AND E.FLDID <> ' '
GROUP BY
E.FLDSUPRNAME
ORDER BY
E.FLDSUPRNAME |
 |
|
|
anishap
Yak Posting Veteran
61 Posts |
Posted - 2013-02-25 : 17:27:02
|
Yes, I need most of the columns on the report. The query works fine when i tried without any changes but I need more info on the report.
here is the revised code but it didn't worked
*********************
SELECT
E.FLDSUPRNAME AS Supervisor,
E.FLDLNAME,E.FLDFNAME,E.FLDID,
SUM(1) AS EmployeeCount,
100*SUM(CASE WHEN I.FLDDATE IS NULL THEN 0 ELSE 1 END)/
SUM(1) AS Percent_Flu_Compliant,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU
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()
WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
AND E.FLDSUPRNAME <> ' '
AND E.FLDID <> ' '
GROUP BY
E.FLDSUPRNAME,E.FLDLNAME,E.FLDFNAME,E.FLDID,I.FLDDATE
ORDER BY
E.FLDSUPRNAME |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 01:33:37
|
does seem to have any obvious syntax issues
whats the purpose of SUM(1) in denominator? is that for getting full count?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
anishap
Yak Posting Veteran
61 Posts |
Posted - 2013-02-26 : 10:40:52
|
The purpose is to find the % compliance.
Number of employee that are compliant/employee count.
SUM(1) is the employee count |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-26 : 23:24:48
|
quote:
Originally posted by anishap
The purpose is to find the % compliance.
Number of employee that are compliant/employee count.
SUM(1) is the employee count
SUM(1) wont give you employee count as you're grouping on E.FLDSUPRNAME,E.FLDLNAME,E.FLDFNAME etc which i reckon are the employee fields. If you're looking at entire employee count you need to change GROUP BY or use syntax like
SUM(1) OVER()
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
|
|
|
|
|
Help with SSRS report