Link to home
Start Free TrialLog in
Avatar of tobin46
tobin46

asked on

SQL And....or....OR

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.



Sample-SQL.txt
ASKER CERTIFIED SOLUTION
Avatar of EL_Barbado
EL_Barbado

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

With the query in thread ID:
please change the Order By in your CTE as
ORDER BY Policy_Number, [Claim#], claim_id, Valuation_Date