Solved

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

Posted on 2011-03-17
2
235 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:tobin46
2 Comments
 
LVL 2

Accepted Solution

by:
EL_Barbado earned 500 total points
ID: 35162063
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 35164253
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now