Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Compare Two records in same table

Posted on 2011-03-15
Medium Priority
345 Views
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
Question by:tobin46
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 11
• 6
• 2
• +1

LVL 19

Expert Comment

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

ID: 35144420
Does the syntax you've provided suggest two tables?
0

LVL 1

Author Comment

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

ID: 35144426
Hi,

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

LVL 19

Expert Comment

ID: 35144435
Hi,

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

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

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

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

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

ID: 35144574
Hi,

Didnt understand what you said.
0

LVL 1

Author Comment

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

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

Sharath earned 1600 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
``````
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
*/
``````
0

LVL 19

Assisted Solution

Bhavesh Shah earned 400 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

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

LVL 1

Author Comment

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

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

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
``````
0

LVL 1

Author Comment

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

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

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

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" passâ€¦
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 video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompaâ€¦
###### Suggested Courses
Course of the Month9 days, 11 hours left to enroll