SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Help with SSRS report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anishap
Yak Posting Veteran

61 Posts

Posted - 02/25/2013 :  13:39:30  Show Profile  Reply with Quote
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.

Edited by - anishap on 02/25/2013 17:24:02

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 02/25/2013 :  14:30:31  Show Profile  Reply with Quote
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
Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 02/25/2013 :  17:27:02  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  01:33:37  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 02/26/2013 :  10:40:52  Show Profile  Reply with Quote
The purpose is to find the % compliance.

Number of employee that are compliant/employee count.

SUM(1) is the employee count
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  23:24:48  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000