I have a table that I've partitioned by claim_id. I'm comparing fields from one partition to another and if they are different I want them in the result set.
So in my WHERE clause, I have:
Where c1.Total <> c2.Total
BUT...if C1.Total <> c2.Total is not satisfied, I also want to see if there are other conditions met....and in the case where both are satisfied, I only want to return the record once, not twice.
Here would be an example
Claim_ID Status Total_Paid Incurred_Amount Eval_Date
1 Open 100 100 12/31/2009
1 Closed 100 100 12/31/2010
In the case above, Total_Paids for Claim_Id is equal so the first condition in my Where clause will not be satisfied, but I also want to pull claims where eval_date = 12/31/2009 and status = Open.
What would SQL look like for that case?
I think the partition is throwing me off....I just want to return a record once if any one of my conditions are met...
I know how many records should be returned, but it seems my results are returning double records. SQL is attached.