Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-03-17
2
Medium Priority
?
292 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

810 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