Replace null values in right outer join with a previous not null matched row

Question

I have this query

select a.WeekNumber
        ,a.filedate
        ,a.customer
        ,material
        ,Quantity
from zfmon zf right outer join zfmonTemp a
on zf.customer = a.customer
        and zf.filedate = a.filedate
        and zf.material =  'AD215BY'

It returns the following

WeekNumber FileDate                 Customer Material Quantity
1          2010-03-19 00:00:00.000  1008777  NULL     NULL
2          2010-03-12 00:00:00.000  1008777  AD215XX  3

What I want is for when the material is null replace it with the next not null value. In this case it would replace it with AD215XX

Therefore the output will look like

WeekNumber FileDate                 Customer Material Quantity
1          2010-03-19 00:00:00.000  1008777  AD215XX  NULL
2          2010-03-12 00:00:00.000  1008777  AD215XX  3

Is that possible to do? Can any one help please.

Thanks, Eli

Answer 1

select a.WeekNumber
        ,a.filedate
        ,a.customer
        ,isnull(material, (select top 1 material from zfmonTemp where weeknumber > zf.weeknumber and material is not null order by weeknumber)) material
        ,Quantity
from zfmon zf right outer join zfmonTemp a
on zf.customer = a.customer
        and zf.filedate = a.filedate
        and zf.material =  'AD215BY'