Solved

Compare Two records in same table

Posted on 2011-03-15
21
298 Views
Last Modified: 2012-06-21
I have a table that has data for claims at different evaluation dates.  There is a record as of 1/1/2010, then another record for 1/1/2011.  

The common fields are claim_number, claim_id, and policy_number.  

To be simple, let's say the table only has 4 columns.  Claim_Number, Claim_Id, Policy_Number, and Total_Paid.

Can I take the record from 1/1/2010 and compare it to the record as of 1/1/2011 to see if the Total_Paid amount is different?  
0
Comment
Question by:tobin46
  • 11
  • 6
  • 2
  • +1
21 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35144410
Hi,

Check out this.

SELECT
            A.Claim_Number, A.Claim_Id, A.Policy_Number, A.Total_Paid, B.Total_Paid
From Table A INNER JOIN Table B
ON A.Claim_Number = B.Claim_Number
AND A.Claim_ID = B.Claim_ID
AND A.Policy_Number = B.Policy_Number
WHERE
      A.Total_paid <> B.Total_Paid



- Bhavesh
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144420
Does the syntax you've provided suggest two tables?
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144425
Also, sorry....let me expand the example a bit above.

The table would have 5 columns....
Claim_Number, Claim_Id, Policy_Number, Total_Paid, and Valuation_Date

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35144426
Hi,

No...
I'm joining same table twice.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35144435
Hi,

Just added one column.


SELECT
            A.Claim_Number, A.Claim_Id, A.Policy_Number, A.Total_Paid, B.Total_Paid
From Table A INNER JOIN Table B
ON A.Claim_Number = B.Claim_Number
AND A.Claim_ID = B.Claim_ID
AND A.Policy_Number = B.Policy_Number
AND A.Valuation_Date = B.Valuation_Date
WHERE
      A.Total_paid <> B.Total_Paid

0
 
LVL 1

Expert Comment

by:geff_chang
ID: 35144476
For the same table:

SELECT
*
FROM
A as AA, A as AB
WHERE
AA.Claim_Number = AB.Claim_Number AND
AA.Claim_Id = AB.Claim_Id AND
AA.Policy_Number = AB.Policy_Number AND
AA.Claim_Number = AB.Claim_Number AND
AA.Total_Paid <> AB.Total_Paid
0
 
LVL 1

Expert Comment

by:geff_chang
ID: 35144479
Correction:

SELECT
*
FROM
A as AA, A as AB
WHERE
AA.Claim_Number = AB.Claim_Number AND
AA.Claim_Id = AB.Claim_Id AND
AA.Policy_Number = AB.Policy_Number AND
AA.Valuation_Date = AB.Valuation_Date AND
AA.Total_Paid <> AB.Total_Paid
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144514
Interesting....I got results and they look good....

One problem.  It looks like the join is sometimes putting claims with valuation date 1/1/2010 on the "left" side of the results (the A side) and then in other records, on the right side (the B side).

How can I make sure all valuation dates of 1/1/2010 are on "left" side of results or the A side?
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144534
@geff_chang......I got results using your method as well....but valuation dates of 1/1/2010 are showing up on "left" side and "right" side of results.....if that makes sense....
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35144574
Hi,

Didnt understand what you said.
Can you please explain?
0
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.

 
LVL 1

Author Comment

by:tobin46
ID: 35144660
See attached sample of results....

I would want results to show valuation date '20091231' to show up on the "left" side....
Sample-Results.xls
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144699
Look like results are being displayed both ways....with valuation dates of 20091231 on "left" side of results and then on "right" side of results.....
0
 
LVL 40

Accepted Solution

by:
Sharath earned 400 total points
ID: 35144720
Are you looking for this?
;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Policy_Number,Claim_Number ORDER BY Valuation_Date) rn 
           FROM your_table) 
SELECT * 
  FROM cte c1 
       JOIN cte c2 
         ON c1.Policy_Number = c2.Policy_Number 
            AND c1.Claim_Number = c2.Claim_Number 
 WHERE c1.rn = 1 
       AND c2.rn = 2 
       AND c1.Paid_Total <> c2.Paid_Total

Open in new window

Here is the result with your data.
DECLARE  @table  TABLE( 
                        Policy_Number INT, 
                         Claim_Number INT, 
                             Claim_Id INT, 
                           Paid_Total DECIMAL(10,2), 
                       Valuation_Date INT 
                       ) 

INSERT @table 
VALUES(12,522799,156070,10279.01,20091231) 

INSERT @table 
VALUES(12,526310,160551,15340.29,20091231) 

INSERT @table 
VALUES(12,522855,156144,17561.59,20091231) 

INSERT @table 
VALUES(12,522799,156070,21703.97,20101231) 

INSERT @table 
VALUES(12,526310,160551,30303.08,20101231) 

INSERT @table 
VALUES(12,522855,156144,25806.97,20101231); 

;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Policy_Number,Claim_Number ORDER BY Valuation_Date) rn 
           FROM @table) 
SELECT * 
  FROM cte c1 
       JOIN cte c2 
         ON c1.Policy_Number = c2.Policy_Number 
            AND c1.Claim_Number = c2.Claim_Number 
 WHERE c1.rn = 1 
       AND c2.rn = 2 
       AND c1.Paid_Total <> c2.Paid_Total
/*
Policy_Number	Claim_Number	Claim_Id	Paid_Total	Valuation_Date	rn	Policy_Number	Claim_Number	Claim_Id	Paid_Total	Valuation_Date	rn
12	522799	156070	10279.01	20091231	1	12	522799	156070	21703.97	20101231	2
12	522855	156144	17561.59	20091231	1	12	522855	156144	25806.97	20101231	2
12	526310	160551	15340.29	20091231	1	12	526310	160551	30303.08	20101231	2
*/

Open in new window

0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 100 total points
ID: 35144721
Hi,

Run my query with additional one field

SELECT
            A.Claim_Number, A.Claim_Id, A.Policy_Number, A.Total_Paid, A.Total_Paid, B.Total_Paid
From Table A INNER JOIN Table B
ON A.Claim_Number = B.Claim_Number
AND A.Claim_ID = B.Claim_ID
AND A.Policy_Number = B.Policy_Number
AND A.Valuation_Date = B.Valuation_Date
WHERE
      A.Total_paid <> B.Total_Paid

0
 
LVL 1

Author Comment

by:tobin46
ID: 35144758
@Brichsoft....looks like you have A.Total_Paid in your syntax twice.....
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144764
Here is the syntax I'm using:

select
A.POLICY_NUMBER,
A.[Claim#],
A.CLAIM_ID,
A.PAID_INDEMNITY,
A.Valuation_date,
B.POLICY_NUMBER,
B.[Claim#],
B.CLAIM_ID,
B.PAID_INDEMNITY,
B.Valuation_date,
A.PAID_INDEMNITY-B.PAID_INDEMNITY as 'Difference'

from

PY08_Claims_ALL as A inner Join PY08_Claims_ALL as B on A.policy_number = B.policy_number
and a.claim_id = b.claim_id
and a.[claim#] = b.[claim#]
and a.PAID_INDEMNITY <> B.PAID_INDEMNITY
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35144791
Hi,

Its not twice.

As you said, you wanted to differentiate Total_Paid from same table based on

Claim_Number, Claim_Id, Policy_Number, Total_Paid, and Valuation_Date

So i had Put Total_Paid for two times with different value.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35144794
Did you try with Row Number?
;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Policy_Number,Claim_Number ORDER BY Valuation_Date) rn 
           FROM your_table) 
SELECT *, 
       c2.PAID_INDEMNITY - c1.PAID_INDEMNITY AS [Difference] 
  FROM cte c1 
       JOIN cte c2 
         ON c1.Policy_Number = c2.Policy_Number 
            AND c1.Claim_Number = c2.Claim_Number 
 WHERE c1.rn = 1 
       AND c2.rn = 2 
       AND c1.Paid_Total <> c2.Paid_Total

Open in new window

0
 
LVL 1

Author Comment

by:tobin46
ID: 35144823
@sharath_123.......that's nice.  You're solution worked.

Would there be a way that I could actually label the fields differently in c2 than c1.

So the fields in c2 would be something like this:
Policy_Number_20101231
Claim_Number_20101231
etc....
                           
Only reason I ask is because I am passing the results to a Vb.Net app and I will actually be taking the one record and then writing two records to a text file....
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144836
@sharath...here's the syntax I came up with using your logic....
;WITH cte
     AS (SELECT POLICY_NUMBER, CLAIM_ID, [CLAIM#],PAID_INDEMNITY, Valuation_date,
                ROW_NUMBER()
                  OVER(PARTITION BY Policy_Number,[Claim#] ORDER BY Valuation_Date) rn
           FROM PY08_Claims_ALL)
SELECT *
  FROM cte c1
       JOIN cte c2
         ON c1.Policy_Number = c2.Policy_Number
            AND c1.[Claim#] = c2.[Claim#]
 WHERE c1.rn = 1
       AND c2.rn = 2
       AND c1.Paid_Indemnity <> c2.Paid_Indemnity
0
 
LVL 1

Author Comment

by:tobin46
ID: 35144861
@sharath_123....I did this and it worked as I think will work best....
;WITH cte
     AS (SELECT POLICY_NUMBER, CLAIM_ID, [CLAIM#],PAID_INDEMNITY, Valuation_date,
                ROW_NUMBER()
                  OVER(PARTITION BY Policy_Number,[Claim#] ORDER BY Valuation_Date) rn
           FROM PY08_Claims_ALL)
SELECT
c1.POLICY_NUMBER as '20091231_Pol_Num',
c1.Claim_Id as '20091231_Claim_Id',
c1.[Claim#] as '20091231_Claim#',
c1.Paid_Indemnity as '20091231_Paid',
c1.Valuation_Date,
c2.POLICY_NUMBER as '20101231_Pol_Num',
c2.Claim_Id as '20101231_Claim_Id',
c2.[Claim#] as '20101231_Claim#',
c2.Paid_Indemnity as '20101231_Paid',
c2.Valuation_Date,
c2.PAID_INDEMNITY -  c1.PAID_INDEMNITY as 'Difference'
  FROM cte c1
       JOIN cte c2
         ON c1.Policy_Number = c2.Policy_Number
            AND c1.[Claim#] = c2.[Claim#]
 WHERE c1.rn = 1
       AND c2.rn = 2
       AND c1.Paid_Indemnity <> c2.Paid_Indemnity
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore 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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

10 Experts available now in Live!

Get 1:1 Help Now