Fill In A Null Cell With Cell From Previous Record

Hi I am using DB2 sql to fill in some missing data in the following table: Person House From To ------ ----- ---- -- 1 586

Solution 1:

It might look something like this:

select 
 person,
 house,
 coalesce(from_date, prev_to_date) from_date,
 casewhen rn =1thencoalesce (to_date, '9999-01-01')
  elsecoalesce(to_date, next_from_date) end to_date
from
(select person, house, from_date, to_date,
 lag(to_date) over (partitionby person orderby from_date nulls last) prev_to_date,
 lead(from_date) over (partitionby person orderby from_date nulls last) next_from_date,
 row_number() over (partitionby person orderby 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.

Solution 2:

createtable Temp
(
 person varchar(2),
 house int,
 from_date date,
 to_date date 
)

insertinto temp values 
(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)


select  A.person,
        A.house,
        isnull(A.from_date,BF.to_date) From_date,
        isnull(A.to_date,isnull(CT.From_date,'9999-01-01')) To_date
from 
((select*,ROW_NUMBER() over (orderby (select0)) rownum from Temp) A leftjoin
 (select*,ROW_NUMBER() over (orderby (select0)) rownum from Temp) BF
 on A.person = BF.person and
 A.rownum = BF.rownum +1)leftjoin
 (select*,ROW_NUMBER() over (orderby (select0)) rownum from Temp) CT 
  on A.person = CT.person and
  A.rownum = CT.rownum -1