September 24, 2012 at 4:27 pm
I'm new to SSRS and T-SQL reporting. The below query works fine but I want the field R.FLDDATEDUE multiple times on the select statements based on R.FLDPHYSICAL value.
I want something like this
R.FLDDATEDUE AS TB WHEN R.FLDPHYSICAL = '110'
R.FLDDATEDUE AS TBSS WHEN R.FLDPHYSICAL = 'TBSS'
R.FLDDATEDUE AS CXRAY WHEN R.FLDPHUSICAL = '109'
Can anyone help me with this?
****************************
SELECT E.FLDID, E.FLDLNAME, E.FLDFNAME, I.FLDDATE, E.FLDDEPT, E.FLDJOB, R.FLDDATEDUE,
F.FLDDATE
FROM
EMPLOYEE E LEFT OUTER JOIN IMMUNE I ON I.FLDEMPLOYEE = E.FLDREC_NUM
LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
LEFT OUTER JOIN EXAM X ON I.FLDADMIN = X.FLDCODE
WHEREI.FLDCLINIC = @clinic AND
I.FLDDATE>= @begin_date AND
I.FLDDATE<= @end_date AND
I.FLDTYPE IN ('109','111') AND
R.FLDPHYSICAL = '110' AND
I.FLDADMIN = @provider AND
E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'
ORDER BY E.FLDLNAME,E.FLDFNAME,I.FLDDATE
September 25, 2012 at 1:51 am
What I believe you are looking for is a CASE statement
CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE ELSE '' END AS TB,
CASE WHEN R.FLDPHYSICAL = 'TBSS' THEN R.FLDDATEDUE ELSE '' END AS TBSS,
CASE WHEN R.FLDPHUSICAL = '109' THEN R.FLDDATEDUE ELSE '' END AS CXRAY
September 25, 2012 at 9:49 am
Thanks for the case statment, it worked fine.
But I have another problem, what i was looking for was to list all records on employee table even if there is no match on immune, reqexam, fittest and exam table. I tried left outer join but I'm not getting all records from the employee table.
Any help on this, I really appreciate. Many thanks for your time.
***********************************
SELECT E.FLDID, E.FLDLNAME, E.FLDFNAME, I.FLDDATE, E.FLDDEPT, E.FLDJOB,
F.FLDDATE AS FIT,CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE ELSE '' 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
LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
LEFT OUTER JOIN EXAM X ON I.FLDADMIN = X.FLDCODE
WHEREI.FLDCLINIC = @clinic AND
I.FLDDATE>= @begin_date AND
I.FLDDATE<= @end_date AND
I.FLDTYPE IN ('109','111') AND
R.FLDPHYSICAL = '110' AND
I.FLDADMIN = @provider AND
E.FLDCOMP = @company AND
E.FLDSTATUS = 'A'
ORDER BY E.FLDLNAME,E.FLDFNAME,I.FLDDATE, R.FLDDATEDUE ASC, F.FLDDATE DESC
September 26, 2012 at 1:10 am
If you could follow the second link in my signature on posting code and data for the best help I'm sure we can get to some working solution
We would need the full table definitions of all tables in the SQL statement, sample data for all tables (you can make this up if the data is sensitive) and based on that sample data what you want the outcome to be.
It might just be a simple case of a join is wrong somewhere but without having some play data to test with, it will be a bit tricky to see what is actually at fault.
September 26, 2012 at 5:50 am
Your left joins are being forced into inner joins because of the criteria in the Where clause. Specifically, when using a left join, any criteria relevant to the tables on the right side of the join need to be part of the join, and not in the Where clause.
The code below should pull all Employee data according to @company and FLDSTATUS='A', and match any data from the remaining tables:
SELECT E.FLDID
,E.FLDLNAME
,E.FLDFNAME
,I.FLDDATE
,E.FLDDEPT
,E.FLDJOB
,F.FLDDATE AS FIT
,CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE
ELSE ''
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
LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL = '110'
LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
LEFT OUTER JOIN EXAM X ON I.FLDADMIN = X.FLDCODE
WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
ORDER BY E.FLDLNAME
,E.FLDFNAME
,I.FLDDATE
,R.FLDDATEDUE ASC
,F.FLDDATE DESC
--Pete
September 26, 2012 at 9:51 am
I tried to incorporate what you suggest and I'm getting more records.
Now I'm getting lots of duplicate records from FITTEST table, I need only the last date ( F.FLDDATE). I tried MAX(F.FLDDATE) but it's not working.
Any suggestion.
******************************
SELECT E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB,
MAX(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
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
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.R.FLDPHYSICAL,R.FLDDATEDUE
ORDER BY E.FLDLNAME, E.FLDFNAME,E.FLDID, I.FLDDATE, E.FLDDEPT, E.FLDJOB, F.FLDDATE,R.FLDPHYSICAL,R.FLDDATEDUE
September 26, 2012 at 11:22 am
Your comment about Max() not working doesn't specify why it isn't working or if you're getting an error.
So, in the meantime, below is code that does take the Max(F.FLDDATE), and uses a GROUP BY clause on the remaining items that are in the Select clause:
SELECT E.FLDID
,E.FLDLNAME
,E.FLDFNAME
,I.FLDDATE
,E.FLDDEPT
,E.FLDJOB
,MAX(F.FLDDATE) AS FIT
,CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE
ELSE ''
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
LEFT OUTER JOIN REQEXAM R ON E.FLDREC_NUM = R.FLDEMPLOYEE
AND R.FLDPHYSICAL = '110'
LEFT OUTER JOIN FITTEST F ON E.FLDREC_NUM = F.FLDEMPLOYEE
LEFT OUTER JOIN EXAM X ON I.FLDADMIN = X.FLDCODE
WHERE E.FLDCOMP = @company
AND E.FLDSTATUS = 'A'
GROUP BY
E.FLDID
,E.FLDLNAME
,E.FLDFNAME
,I.FLDDATE
,E.FLDDEPT
,E.FLDJOB
,CASE WHEN R.FLDPHYSICAL = '110' THEN R.FLDDATEDUE
ELSE ''
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
,I.FLDDATE
,R.FLDDATEDUE ASC
,F.FLDDATE DESC
--Pete
September 26, 2012 at 12:50 pm
n/a
September 26, 2012 at 3:47 pm
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 a single row. How can I put this is in one singel row? Please let me know
See the attached sample report.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply