All Forums Database
CCSlice 36 posts Joined 03/14
11 Jun 2014
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
 
 

Raja_KT 1246 posts Joined 07/09
11 Jun 2014

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

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CCSlice 36 posts Joined 03/14
12 Jun 2014

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

Raja_KT 1246 posts Joined 07/09
13 Jun 2014

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.

 

 

 

 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CCSlice 36 posts Joined 03/14
16 Jun 2014

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

You must sign in to leave a comment.