Help with SSRS - 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

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-09-28 : 12:15:50

I'm new to SSRS and T-SQL. Can anyone help me with this.?

I found a problem with the below query, if someone has a record for FLDPHYSICAL '110','TBSS' and '109' it creates 3 seprate rows instead of listing in a single row. How can I list this is in one single row? Please let me know

For example: here its showing in 2 rows

name EID Flu TB TBSS CXRAY Fit
Paul 677 10/2/2011 06/1/2011 3/02/2012
Paul 677 10/2/2011 06/1/2012

thanks in advance for your help.

************************************************

SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
F.FLDDATE AS FIT,

CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE END AS TB,
CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN R.FLDDATEDUE END AS TBSS,
CASE WHEN R.FLDPHYSICAL = '109' THEN R.FLDDATEDUE END AS CXRAY

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
R.FLDPHYSICAL,
R.FLDDATEDUE,
F.FLDDATE,

CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE END,
CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN R.FLDDATEDUE END,
CASE WHEN R.FLDPHYSICAL = '109' THEN R.FLDDATEDUE END

ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB, R.FLDPHYSICAL,R.FLDDATEDUE ASC, F.FLDDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 12:41:53

[code]
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
F.FLDDATE AS FIT,

MAX(CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE END) AS TB,
MAX(CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN R.FLDDATEDUE END) AS TBSS,
MAX(CASE WHEN R.FLDPHYSICAL = '109' THEN R.FLDDATEDUE END) AS CXRAY
FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
F.FLDDATE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB ASC, F.FLDDATE DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-09-28 : 13:13:38

I tried the above query, but still i'm getting multiple rows for same person.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 13:42:11

then I think you've different values coming for same person in either of date fields (I.FLDDATE or F.FLDDATE)
show some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-09-28 : 16:47:41

Here is a sample output. Please look at Adelha's record.

This person has TBSS and CXRAY records showing in separate rows.

*******************************

Name I.FLDDATE TB TBSS CXRAY F.FITDATE
KAYI 9/28/2011 12:00:00 AM 6/29/2010 12:00:00 AM
ANGEL 9/29/2011 12:00:00 AM 9/1/2011 12:00:00 AM
SARAH 10/5/2011 12:00:00 AM 10/19/2010 12:00:00 AM
ASHLE 9/28/2011 12:00:00 AM 10/18/2010 12:00:00 AM 10/18/2010 12:00:00 AM
SOFI 10/13/2011 12:00:00 AM
ZAHR 9/28/2011 12:00:00 AM 6/9/2010 12:00:00 AM
HAYA 11/10/2011 12:00:00 AM
MARTI 9/30/2011 12:00:00 AM
SEMIR 9/29/2011 12:00:00 AM 5/10/2012 12:00:00 AM
ADELHA 10/12/2011 12:00:00 AM 10/1/2007 12:00:00 AM
ADELHA 10/12/2011 12:00:00 AM 3/22/2011 12:00:00 AM
LEM 9/28/2011 12:00:00 AM 11/22/2011 12:00:00 AM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 17:02:23

as suspected you've different values coming for F.FITDATE which is the issue. so if you want to make it one record which value should you retrieve for FITDATE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-09-28 : 17:09:45

I don't think you understood what I was talking about. In this case there is no value for F.FITDATE. Here the problem is with R.FLDDATEDUE (TBSS, CXRAY). I want both values but it's showing in separate rows.

For ADELHA 10/1/2007 is for TBSS
3/22/2011 is for CXRAY

There is no value for F.FLDDATE its blank.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 17:14:32

if you're using my exact query and still getting dates in different rows then i'm sure you've unique values coming in any of the below columns for the two rows. As you've not shown us full resultset we can only guess!

E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
F.FLDDATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-09-28 : 18:00:08

Below is the exact output from the query. I didn't find unique values for both rows for
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
F.FLDDATE

Colums heading for the output

EID, Lname,Fname, Dept, I.FLDDATE, TB,TBSS,CXRAY,F.FLDDATE, JOB

8580047 ABDU ADELHA 318105 10/12/2011 12:00:00 AM 10/1/2007 12:00:00 AM 1049
8580047 ABDU ADELHA 318105 10/12/2011 12:00:00 AM 3/22/2011 12:00:00 AM 1049

EID, LNAME, FNAME, DEPTI.FLDDATE, JOB is same in both rows.
TB and F.FLDDATE is blank, but there are unique values for TBSS and CXRAY.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 11:19:59

I'm sure there's some part of query you're not giving us full details on. If above is correct and its only columns used in SELECT and GROUP directly without any aggregations you should be having only one row for every value combination of these columns
Thats all what we can say based on provided information. What exactly is the problem we cant guess unless we've the full picture

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-01 : 12:41:17

Ok, I will check again. Anyway I'm not sure what more details I need to give or what I'm missing from the above.

Thanks for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 12:57:28

Welcome!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-02 : 19:08:50

The above query works fine. Sorry to bother again, now I have another problem.

I want to check whether the output from each column is compliant or not.
For example: CASE WHEN F.FLDDATE IS NULL OR YEAR([F.FLDDATE]) < YEAR(GETDATE()) THEN 'NO' ELSE 'YES' END AS Fit_Compliant,
CASE WHEN R.FLDPHYSICAL = '110' AND R.FLDDATEDUE IS NULL THEN 'NO' ELSE 'YES' END As TB_Compliant

When I tried to incorporate the above case statement for F.FLDDATE, I'm getting an error "Invalid column name F.FLDDATE" and
Column R.FLDPHYSICAL is invalid.

Please help me.

*******************************************
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Complaint,
MAX(CASE WHEN R.FLDPHYSICAL = '110' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TB ,
MAX(CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TBSS,
MAX(CASE WHEN R.FLDPHYSICAL = '109' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS CXRAY

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN','MBRN','JGRN','RHMN','EMRN','ERMA','LYND','MDRN','KAEM'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
F.FLDDATE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB ASC, F.FLDDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 10:49:09

so far as column is included in GROUP BY (which I'm seeing above) you shouldnt have any problems.

B/w where are you trying to include case statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-03 : 11:27:13

Please see the below query.

When add the statement: CASE WHEN F.FLDDATE IS NULL OR YEAR([F.FLDDATE]) < YEAR(GETDATE()) THEN 'NO' ELSE 'YES' END AS Fit_Compliant

I'm getting an error F.FLDDATE not found.

It works when I just say: CASE WHEN F.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Fit_Compliant. So I guess there is some problem with checking the year.

Also I'm not sure how to incorporate "WHEN R.FLDPHYSICAL = '110' AND R.FLDDATEDUE IS NULL THEN 'NO' ELSE 'YES' END As TB_Compliant" on the below case statement.

MAX(CASE WHEN R.FLDPHYSICAL = '110' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TB.

Please let me know.
****************************************************
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Complaint,
CASE WHEN F.FLDDATE IS NULL OR YEAR([F.FLDDATE]) < YEAR(GETDATE()) THEN 'NO' ELSE 'YES' END AS Fit_Compliant,
MAX(CASE WHEN R.FLDPHYSICAL = '110' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TB ,
MAX(CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TBSS,
MAX(CASE WHEN R.FLDPHYSICAL = '109' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS CXRAY

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN','MBRN','JGRN','RHMN','EMRN','ERMA','LYND','MDRN','KAEM'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
F.FLDDATE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB ASC, F.FLDDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 12:22:59

it should be
YEAR(F.[FLDDATE]) and not
YEAR([F.FLDDATE])

other condition if you want to use you've to add an aggregate function like MIN,MAX etc as R.FLDPHYSICAL,R.FIELDDATEDUE etc are not part of GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-03 : 13:02:20

Thanks YEAR(F.[FLDDATE]) worked fine.

I updated the below query but still Tb_compliant is not working as expected. I'm getting 'YES' for all rows even if it's NULL.
Do you see any problem?

---------------------
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Complaint,
CASE WHEN F.FLDDATE IS NULL OR YEAR(F.[FLDDATE]) < YEAR(GETDATE()) THEN 'NO' ELSE 'YES' END AS Fit_Compliant,

MAX(CASE WHEN R.FLDPHYSICAL = '110' AND R.FLDDATEDUE IS NULL THEN 'NO' ELSE 'YES' END) AS Tb_Compliant, CONVERT(VARCHAR(10),R.FLDDATEDUE,101) AS TB,
MAX(CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TBSS,
MAX(CASE WHEN R.FLDPHYSICAL = '109' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS CXRAY

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN','MBRN','JGRN','RHMN','EMRN','ERMA','LYND','MDRN','KAEM'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
F.FLDDATE,
R.FLDPHYSICAL,
R.FLDDATEDUE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB ASC, F.FLDDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 13:09:00

i think what you need is this

CASE WHEN SUM(CASE WHEN R.FLDPHYSICAL = '110' AND R.FLDDATEDUE IS NULL THEN 1 ELSE 0 END) > 0 THEN 'NO' ELSE 'YES' END AS Tb_Compliant

rather than what you have now as you've multiple values for FLDPHYSICAL and FLDDATEDUE within your applied group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-03 : 13:58:13

I modified the query as you mentioned above but still I'm getting YES for all rows.

Please see the below query.

*********************
SELECT
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
CONVERT(VARCHAR(10),I.FLDDATE, 101) AS FLU,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
CONVERT(VARCHAR(10),F.FLDDATE, 101) AS FIT,
CASE WHEN I.FLDDATE IS NULL THEN 'NO' ELSE 'YES' END AS Flu_Complaint,
CASE WHEN F.FLDDATE IS NULL OR YEAR(F.[FLDDATE]) < YEAR(GETDATE()) THEN 'NO' ELSE 'YES' END AS Fit_Compliant,

CASE WHEN SUM(CASE WHEN R.FLDPHYSICAL = '110' AND R.FLDDATEDUE IS NULL THEN 1 ELSE 0 END) > 0 THEN 'NO' ELSE 'YES' END AS Tb_Compliant,
MAX(CASE WHEN R.FLDPHYSICAL = '110' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TB,
MAX(CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS TBSS,
MAX(CASE WHEN R.FLDPHYSICAL = '109' THEN CONVERT(VARCHAR(10),R.FLDDATEDUE, 101) END) AS CXRAY

FROM
EMPLOYEE E
LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM AND
I.FLDCLINIC = @clinic AND
I.FLDDATE >= @begin_date AND I.FLDDATE <= @end_date AND
I.FLDTYPE IN ('109','111') AND
(I.FLDADMIN = @provider OR I.FLDADMIN IN ('MKRN','SOI','MDN','MBRN','JGRN','RHMN','EMRN','ERMA','LYND','MDRN','KAEM'))

LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL IN ('110','TBSS','109') AND
R.FLDDATEDUE <= @end_date
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 EXAM X ON I.FLDADMIN = X.FLDCODE

WHERE

E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'

GROUP BY
E.FLDLNAME,
E.FLDFNAME,
E.FLDID,
I.FLDDATE,
E.FLDDEPT,
E.FLDJOB,
E.FLDSUPRNAME,
F.FLDDATE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB ASC, F.FLDDATE DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-03 : 18:00:36

then i would say you need to explain the rules for determining value. You need to consider fact that there will be multiple record values involved as you're doing GROUP ing. so how should your rules be for setting value for Tb_Complaint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anishap
Yak Posting Veteran

61 Posts

Posted - 2012-10-03 : 19:11:32

I'm not sure how to set the rules.

Here is the scenario, If an employee is due for TB or TBSS or CXRAY and due date is <= today's date, then it's non-compliant other wise compliant.

If FLDPHYSICAL = '110' OR
FLDPHYSICAL = 'TBSS' OR
FLDPHYSICAL = '109' AND
FLDDATEDUE IS NULL
THEN Compliant ELSE
Non_Compliant

Any thoughts on fixing this? Sorry to bother you again.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources