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
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

anishap
Yak Posting Veteran

61 Posts

Posted - 09/28/2012 :  12:15:50  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/28/2012 :  12:41:53  Show Profile  Reply with Quote

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 


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

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 09/28/2012 :  13:13:38  Show Profile  Reply with Quote
I tried the above query, but still i'm getting multiple rows for same person.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  13:42:11  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 09/28/2012 :  16:47:41  Show Profile  Reply with Quote

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  17:02:23  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 09/28/2012 :  17:09:45  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  17:14:32  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 09/28/2012 :  18:00:08  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/01/2012 :  11:19:59  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/01/2012 :  12:41:17  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/01/2012 :  12:57:28  Show Profile  Reply with Quote
Welcome!


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

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/02/2012 :  19:08:50  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/03/2012 :  10:49:09  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/03/2012 :  11:27:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/03/2012 :  12:22:59  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/03/2012 :  13:02:20  Show Profile  Reply with Quote
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


Edited by - anishap on 10/03/2012 13:04:04
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/03/2012 :  13:09:00  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/03/2012 :  13:58:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/03/2012 :  18:00:36  Show Profile  Reply with Quote
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/

Go to Top of Page

anishap
Yak Posting Veteran

61 Posts

Posted - 10/03/2012 :  19:11:32  Show Profile  Reply with Quote
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.

Edited by - anishap on 10/04/2012 11:15:24
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.17 seconds. Powered By: Snitz Forums 2000