Solved

Compare Two records in same table

Posted on 2011-03-15
21
331 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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
 
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 41

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 41

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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