fill in a null cell with cell from previous record

Question

Hi I am using DB2 sql to fill in some missing data in the following table:

Person       House     From           To      
------       -----     ----           --
1            586       2000-04-16     2010-12-03 
2            123       2001-01-01     2012-09-27
2            NULL      NULL           NULL
2            104       2004-01-01     2012-11-24
3            987       1999-12-31     2009-08-01
3            NULL      NULL           NULL

Where person 2 has lived in 3 houses, but the middle address it is not known where, and when. I can't do anything about what house they were in, but I would like to take the previous house they lived at, and use the previous To date to replace the NULL From date, and use the next address info and use the From date to replace the null To date ie.

Person       House     From           To      
------       -----     ----           --
1            586       2000-04-16     2010-12-03 
2            123       2001-01-01     2012-09-27
2            NULL      2012-09-27     2004-01-01
2            104       2004-01-01     2012-11-24
3            987       1999-12-31     2009-08-01
3            NULL      2009-08-01     9999-01-01

I understand that if there is no previous address before a null address, that will have to stay null, but if a null address is the last know address I would like to change the To date to 9999-01-01 as in person 3.

This type of problem seems to me where set theory no longer becomes a good solution, however I am required to find a DB2 solution because that's what my boss uses!

any pointers/suggestions welcome.

Thanks.

Answer 1

It might look something like this:

select 
 person,
 house,
 coalesce(from_date, prev_to_date) from_date,
 case when rn = 1 then coalesce (to_date, '9999-01-01')
  else coalesce(to_date, next_from_date) end to_date
from
(select person, house, from_date, to_date,
 lag(to_date) over (partition by person order by from_date nulls last) prev_to_date,
 lead(from_date) over (partition by person order by from_date nulls last) next_from_date,
 row_number() over (partition by person order by from_date desc nulls last) rn
 from temp
) t

The above is not tested but it might give you an idea.

I hope in your actual table you have a column other than to_date and from_date that allows you to order rows for each person, otherwise you'll have trouble sorting NULL dates, as you have no way of knowing the actual sequence.