Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Compare Two records in same table

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
tobin46
Asked:
tobin46
  • 11
  • 6
  • 2
  • +1
2 Solutions
 
Bhavesh ShahLead AnalysistCommented:
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
 
tobin46Author Commented:
Does the syntax you've provided suggest two tables?
0
 
tobin46Author Commented:
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
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.

 
Bhavesh ShahLead AnalysistCommented:
Hi,

No...
I'm joining same table twice.
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
geff_changCommented:
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
 
geff_changCommented:
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
 
tobin46Author Commented:
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
 
tobin46Author Commented:
@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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Didnt understand what you said.
Can you please explain?
0
 
tobin46Author Commented:
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
 
tobin46Author Commented:
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
 
SharathData EngineerCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
tobin46Author Commented:
@Brichsoft....looks like you have A.Total_Paid in your syntax twice.....
0
 
tobin46Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
SharathData EngineerCommented:
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
 
tobin46Author Commented:
@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
 
tobin46Author Commented:
@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
 
tobin46Author Commented:
@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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now