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
21
Medium Priority
?
345 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
[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
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…

722 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