CODEWITHSUNDEEP
sqlvisual-studioreporting-services
dragos_kai
dragos_kai

Reputation: 110

SSRS - Display fields based on criteria from two different datasets

I'm working on a SSRS report where I am pulling claim data from two different datasets (creatively named DataSet1 and DataSet2) and it is creating two separate tables and information here:

enter image description here

You'll see the fields pretty easily spelled out, what I am looking to do is create another table with data that displays ONLY data that is not matching in both, so in the example given, it would display the claim no, trans date, and amount of everything other than CLAIM987654321 (which is the only unique identifier, as with the way things are processed the dates may be different.)

I know how to display only based on a query, but am unsure how to make the multi-dataset comparison happen.

Sadly there is no possible way to have the data combine to my knowledge, there may be, but I am unsure how to do this. Below are the queries I am using within SSMS.

enter image description here enter image description here

The servers are already linked per previous joins, but if there is a way to manipulate the data into a single pull, I am unfamiliar with that.

NEW UPDATE: I threw together a really ugly linked server pull, but it is only pulling the data that exists in both, and I would want the data that is NOT as well.enter image description here

Upvotes: 0

Views: 221

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

You'll need to use FULL JOIN

So if you want to see only data that does not appear in both tables then I would do something like this. (I've not used you full qualifiers for clarity but you'll get the idea)

SELECT 
          COALESCE(c.ClaimNo, r.CHK_claim_number) AS [Claim Number] -- COALESCE will get first non null value
        , COALESCE(d.OtherPayer1Paid, r.CHK_payable_cost) AS [Amount]
        , COALESCE(c.TransactionDate, d.CHK_paid_date) AS [Transaction Date]
    FROM EDI_Claims c -- Full join shows all records, null will show for missing records
        JOIN EDI_ClaimDetails d ON c.id =d.claimid
        FULL JOIN PaidClaims_by_CheckRun r ON r.CHK_claim_number = c.claimno
    WHERE d.OtherPayer1Paid != 0 
        AND (r.CHK_ClaimNUmber IS NULL OR c.ClaimNo IS NULL) -- only show when one side of the join fails
    ORDER BY c.TrandactionDate

Upvotes: 1

Related Questions