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

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

---

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'

---

set @material = 'AD215BY';
select a.WeekNumber
        ,a.filedate
        ,a.customer
        ,coalesce(zf.material, @material) as Material
        ,zf.Quantity
from zfmon zf right outer join zfmonTemp a
on zf.customer = a.customer
        and zf.filedate = a.filedate
        and zf.material = @material