Finding the difference between two records in a table.

Database

Finding the difference between two records in a table.

Hi Everyone,

Here's the challenge:  Within a table, I have client profiles.  There are two profiles:  The last update of the profile and then the previous update of the profile.   What I need to do is to find the difference between the two records (if there are two.. some profiles have the one client profile).

The table is set up in this way:

CLNT_ID, LST_RVW_DT, PERMISS_CD, PROD_NM, ADDR, LST_TMSTMP

Data would look like

1213, 05/30/2014, ?, ?, CA,05/31/2014 12:11:08.115251

1213, 05/29/2014,?,?, CA, 05/29/2014 13:15:08.252553

Thanks

4 REPLIES

Re: Finding the difference between two records in a table.

Thinking of  UNION of SELECT :

example pseudo:

SELECT ......min(LST_RVW_DT) 

   over (partition by clnt_id 

         order by LST_TMSTMP

         rows between 1 preceding and 1 preceding) as ist_result

from tab

UNION 

SELECT ......

MAX(LST_RVW_DT) 

   over (partition by clnt_id 

         order by LST_TMSTMP

         rows between 1 preceding and 1 preceding) as 2ND_result

from tab

Re: Finding the difference between two records in a table.

Thanks Raja for the response.  From this point, how would I identify the columns in which the information changed?

Thanks.

Re: Finding the difference between two records in a table.

Hope this helps,

example:

SELECT * FROM( 

SELECT 'FROM_A' as A1, A.ID, A.COL1, A.COL2, A.COL3, ...

  FROM from tab

  UNION ALL

  SELECT 'FROM_B' as A2, B.ID, B.COL1, B.COl2, B.COL3, ...

  FROM from tab) GV

'FROM_A' aliased as A1 and it is just a word.

Re: Finding the difference between two records in a table.

Raja, will this actually show the difference between one record to another?  I am looking to identify the differences and perhaps identify the columns in which the differences occur.

For example using the previous record above:

id, creat_date , imp, clck, region, st_tmstmp 

1213, 05/30/2014, ?, ?, CA,05/31/2014 12:11:08.115251

1213, 05/29/2014,?,?, CA, 05/29/2014 13:15:08.252553

so what has changed taking the latest record update as the 1st record:

creat_date 05/30/2014 st_tmstmp 05/31/2014 12:11:08:115251

Thanks