Find column value in different row

Analytics
Enthusiast

Find column value in different row

Hello - I need help writing the code for the following scenario. I appreciate any insight. Thanks!

 

  1. Table 1 - set of records by id, date and comp
  2. Table 2 - comp can change over time, so need to join to bridge table to identify all possible new for each comp
  3. Table 3 - from the second table need to identify the disposition of a comp
    1. disp is the same comp completed after the previous same comp
      1. since comp can change need to evaluate new to see if it could fulfill disp for a comp
      2. examples below
        1. id = 140 --> comp did not change and new was not completed
        2. id = 76898 --> comp changed from 13209 to 19808, so disp for 13209 is new = 19808

 

Table 1:

IdDateComp
1403/9/201318418
1404/12/201318418
1404/26/201316868
14010/17/201418841
1401/29/201616703
768982/28/201318839
768982/15/201413209
7689812/21/201619808

 

Table 2: 

IdEnd DateComp KeyNew Key
1403/9/20131841819808
1404/12/20131841819808
1404/26/201316868 
14010/17/201418841 
1401/29/201616703 
768982/28/201318839 
768982/15/20141320918398
768982/15/20141320918399
768982/15/20141320918403
768982/15/20141320918407
768982/15/20141320918412
768982/15/20141320918417
768982/15/20141320918418
768982/15/20141320919803
768982/15/20141320919805
768982/15/20141320919806
768982/15/20141320919808
768982/15/20141320920329
768982/15/20141320920333
768982/15/20141320920337
7689812/21/201619808 

 

Table 3:

IdEnd DateComp KeyNew KeyDisp KeyDisp Dt
1403/9/20131841819808184184/12/2013
1404/12/20131841819808  
1404/26/201316868   
14010/17/201418841   
1401/29/201616703   
768982/28/201318839   
768982/15/20141320918398  
768982/15/20141320918399  
768982/15/20141320918403  
768982/15/20141320918407  
768982/15/20141320918412  
768982/15/20141320918417  
768982/15/20141320918418  
768982/15/20141320919803  
768982/15/20141320919805  
768982/15/20141320919806  
768982/15/201413209198081980812/21/2016
768982/15/20141320920329  
768982/15/20141320920333  
768982/15/20141320920337  
7689812/21/201619808