Help with SSRS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Help with SSRS

Help with SSRS

Rate Topic

Display Mode

Topic Options

Author

Message

abrahamanish

Posted Monday, September 24, 2012 4:27 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 4:22 PM
Points: 11, Visits: 30

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

WHERE I.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

Post #1363746

anthony.green

Posted Tuesday, September 25, 2012 1:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363839

abrahamanish

Posted Tuesday, September 25, 2012 9:49 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 4:22 PM
Points: 11, Visits: 30

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

WHERE I.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

Post #1364149

anthony.green

Posted Wednesday, September 26, 2012 1:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1364467

peterzeke

Posted Wednesday, September 26, 2012 5:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 285, Visits: 1,204

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

Post #1364590

abrahamanish

Posted Wednesday, September 26, 2012 9:51 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 4:22 PM
Points: 11, Visits: 30

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

Post #1364804

peterzeke

Posted Wednesday, September 26, 2012 11:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 285, Visits: 1,204

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

Post #1364851

abrahamanish

Posted Wednesday, September 26, 2012 12:50 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 4:22 PM
Points: 11, Visits: 30

n/a

Post #1364894

abrahamanish

Posted Wednesday, September 26, 2012 3:47 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 01, 2013 4:22 PM
Points: 11, Visits: 30

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

Post Attachments

sample.JPG (1 view, 110.41 KB)

Post #1364960

« Prev Topic | Next Topic »

Permissions