Copy over data from previous rows in oracle 11g table

oracle

Our DB is Oracle 11g. I have a table with the structure is as shown below:

Person_Id |  Eff_Dt    |   Wid | Prv_Wid
----------------------------------

P1       |   1/1/2001   | 2001 |    
P1       |   1/10/2001  | 2001 |  
P1       |   10/10/2001 | 2002 |  
P1       |   1/1/2002   | 2003 |  
P1       |   5/4/2002   | 2003 |     
P1       |   8/6/2002   | 2002 |  
P1       |   1/1/2005   | 2001 |  
P1       |   1/10/2006  | 2001 |

I have a requirement that the Prv_Wid should be derived from the previous row's WID. But if the current and previous WIDs are same, then I have to go to the old previous row where the WID is different and put it in my current row's Prv_Wid.
It should be as shown below:

Person_Id | Eff_Dt | Wid| Prv_Wid
----------------------------

P1        | 1/1/2001    |    2001   |    0  
P1        | 1/10/2001   |    2001   |    0  
P1        | 10/10/2001  |    2002   |  2001  
P1        |  1/1/2002   |    2003   |  2002  
P1        |  5/4/2002   |    2003   |  2002  
P1        |  8/6/2002   |    2002   |  2003  
P1        |  1/1/2005   |    2001   |  2002  
P1        | 1/10/2006   |    2001   |  2002

I have tried several ways like lead, lag, first_value, last_value and procedure to achieve. But, I am not successful. Could you please provide any solution?

Best Answer

All right, so we need some mechanism to keep track of the previous wid value regardless of the number of repeating values, so we can not use LAG directly, because we do not know how far back was the last different value.

Our data is:

with data as
(
  select 'P1' as person_id, date'2001-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-01-10' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-10-10' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2002-01-01' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-05-04' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-08-06' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2005-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2006-01-10' as eff_dt, 2001 as wid from dual
)
select
 *
from
 data
order by
 eff_dt
;

PE EFF_DT            WID
-- ---------- ----------
P1 2001-01-01       2001
P1 2001-01-10       2001
P1 2001-10-10       2002
P1 2002-01-01       2003
P1 2002-05-04       2003
P1 2002-08-06       2002
P1 2005-01-01       2001
P1 2006-01-10       2001

I will not repeat the with section any more to save space. Let's calculate a new field, called prev_marker as:

If wid = previous wid, this field becomes NULL. If wid != previous wid, this field gets the value of the previous wid

select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
order by
  eff_dt
;

PE EFF_DT            WID PREV_MARKER
-- ---------- ---------- -----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002        2001
P1 2002-01-01       2003        2002
P1 2002-05-04       2003 (null)
P1 2002-08-06       2002        2003
P1 2005-01-01       2001        2002
P1 2006-01-10       2001 (null)

Using this coluumn, we can get the desired prv_wid value with the LAST_VALUE function if we inspect rows up to the current row. The trick is, that the LAST_VALUE function supports the IGNORE NULLS option, so when we encounter a NULL value, we can easily ignore all the rows with the same wid and fall back to the previous value:

select
  person_id,
  eff_dt,
  wid,
  last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;

PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002

Our last task is to replace the NULLs with 0s, that is just a simple NVL:

select
  person_id,
  eff_dt,
  wid,
  nvl(last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row), 0) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;


PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001          0
P1 2001-01-10       2001          0
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002
Related Question