How do I perform a data smear in SQL-CodePudding

So I have data that is like this:

row    EMPREF   HOURS
---------------------
1      0023     NULL
2      0023     NULL
3      0023     NULL
4      0023     NULL
5      0023     20
6      0023     NULL
7      0023     NULL
8      0023     35
9      0023     0

So I need the data to 'smear' upwards giving:

row    EMPREF   HOURS
---------------------
1      0023     20
2      0023     20
3      0023     20
4      0023     20
5      0023     20
6      0023     35
7      0023     35
8      0023     35
9      0023     0

I can't use Ignore Nulls.

CodePudding user response：

select t.*,
case when t.val is null then
    (select t3.val from #t as t3 where t3.rownum=
        (select min(t2.rownum) from #t as t2 where t2.rownum>t.rownum and t2.val>0))
else    
    t.val
end as smeared_val
from
#t as t
order by t.rownum

CodePudding user response：

Due to NULL you can group data with count

select row, EMPREF, max(HOURS) over(partition by g) hours
from (
  select *, count(HOURS) over(partition by EMPREF order by row desc) g
  from tbl 
) t
order by EMPREF, row