Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with SSRS report Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:22 PM
Points: 11, Visits: 30
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

Post #1424193
Posted Tuesday, February 26, 2013 2:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:53 PM
Points: 16, Visits: 366
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.


Post #1424258
Posted Tuesday, February 26, 2013 3:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:22 PM
Points: 11, Visits: 30
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
Post #1424262
Posted Wednesday, February 27, 2013 7:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:53 PM
Points: 16, Visits: 366
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?
Post #1424520
Posted Wednesday, February 27, 2013 7:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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.
Post #1424529
Posted Wednesday, February 27, 2013 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:22 PM
Points: 11, Visits: 30
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, PATRICIA 100.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
Post #1424585
Posted Wednesday, February 27, 2013 9:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:53 PM
Points: 16, Visits: 366
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.
Post #1424592
Posted Wednesday, February 27, 2013 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:22 PM
Points: 11, Visits: 30
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?
Post #1424610
Posted Wednesday, February 27, 2013 9:41 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 4:16 AM
Points: 33, Visits: 236
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
Post #1424613
Posted Wednesday, February 27, 2013 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 4:22 PM
Points: 11, Visits: 30
Ok, thanks for the insight.

I will try that.
Post #1424617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse