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
LVL 1
tobin46Asked:
Who is Participating?
 
EL_BarbadoConnect With a Mentor Commented:
I don't see "Where c1.Total <> c2.Total" in the sql that you've attached. However, based on your earlier description I think what you intend to do is:

Where (c1.Total <> c2.Total) OR (eval_date = 12/31/2009 AND status = Open)
0
 
rajeevnandanmishraCommented:
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

0
All Courses

From novice to tech pro — start learning today.