| Author |
 Topic  |
|
anishap
Yak Posting Veteran
61 Posts |
 Posted - 09/28/2012 : 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
India
48084 Posts |
Posted - 09/28/2012 : 12:41:53
|
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/
|
 |
|
|
anishap
Yak Posting Veteran
61 Posts |
Posted - 09/28/2012 : 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
India
48084 Posts |
Posted - 09/28/2012 : 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 - 09/28/2012 : 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
India
48084 Posts |
Posted - 09/28/2012 : 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 - 09/28/2012 : 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
India
48084 Posts |
Posted - 09/28/2012 : 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 - 09/28/2012 : 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
India
48084 Posts |
Posted - 10/01/2012 : 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 - 10/01/2012 : 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
India
48084 Posts |
Posted - 10/01/2012 : 12:57:28
|
Welcome!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
anishap
Yak Posting Veteran
61 Posts |
Posted - 10/02/2012 : 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
India
48084 Posts |
Posted - 10/03/2012 : 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 - 10/03/2012 : 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
India
48084 Posts |
Posted - 10/03/2012 : 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 - 10/03/2012 : 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
|
Edited by - anishap on 10/03/2012 13:04:04 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48084 Posts |
Posted - 10/03/2012 : 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 - 10/03/2012 : 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
India
48084 Posts |
Posted - 10/03/2012 : 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 - 10/03/2012 : 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. |
Edited by - anishap on 10/04/2012 11:15:24 |
|
|
|
Topic |
|
|
|
Help with SSRS
