Solved

Compare Two records in same table

Posted on 2011-03-15
21
307 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

16 Experts available now in Live!

Get 1:1 Help Now