Help with SSRS report - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Help with SSRS report

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources