Set based solution to update nulls to Last Known Value in ordered table

To maintain my purity and honor as a database dork, I wish to update a set of datetimes and floats that has some nulls in the float column such that each null value will be replaced by the previous (by datetime) non-null value.

My environment is mssql 2k8R2.

I hope the following snippet explains my victory conditions sufficiently.

Cheers and Thanks.

create table #datesAndValues(
    [Date] datetime,
    Val float);
create table #resultsShouldLookLikeThis(
    [Date] datetime,
    Val float);

insert into #datesAndValues 
values  
    ('1/8/11',1.1),
    ('1/7/11',null),
    ('1/6/11',1.2),
    ('1/5/11',null),
    ('1/4/11',null),
    ('1/3/11',1.3),
    ('1/2/11',null),
    ('1/1/11',null);

/*  
    set based code (no loops!!!) goes here to set nulls
    in the above table to the last known good (not null) value,
    t开发者_JAVA技巧hereby making it look like the following table.
*/

insert into #resultsShouldLookLikeThis
values  
    ('1/8/11',1.1),
    ('1/7/11',1.2),
    ('1/6/11',1.2),
    ('1/5/11',1.3),
    ('1/4/11',1.3),
    ('1/3/11',1.3),
    ('1/2/11',null),
    ('1/1/11',null);

--this will tell us if it worked
select 'In old but not new' as [WhichTable], o.*
from    (select * from #datesAndValues
        except select * from #resultsShouldLookLikeThis) o
union all
select 'In new but not old' as [WhichTable], n.*
from    (select * from #resultsShouldLookLikeThis
        except select * from #datesAndValues) n;

drop table #datesAndValues;
drop table #resultsShouldLookLikeThis;

update #datesAndValues set
  Val = (select top 1 Val
         from #datesAndValues as T2
         where
          T2.[Date] < T.[Date] and
          T2.Val is not null
         order by [Date] desc)
from #datesAndValues as T         
where T.Val is null

There are some things to note here.

The second level of Inline SQL is required otherwise you get an ambiguous #datesAndValues on the update. (maybe there's a better way to solve that)

The second thing to note is that two of the values will still be null since there is no previous value for 1/1/11 and 1/2/11

Finally as Martin notes. A cursor solution is probably better anyway

UPDATE
    #datesAndValues 
SET 
   Val = newVal.val
FROM
    #datesAndValues d1
    INNER JOIN 
    (SELECT 
       maxVal.OldDate,
       newValue.Val 
     FROM 
       #datesAndValues newValue
        INNER JOIN 
        (
                SELECT
                  t.[Date] oldDate, max(t2.[Date]) NewDate
                 FROM 
                   #datesAndValues   t
                   INNER JOIN #datesAndValues  t2
                   ON t.[Date] > t2.Date
                      and t2.Val is not null
                 WHERE
                   t.Val is null

                 GROUP BY
                   t.[Date]
        )  maxVal
        on NewValue.[Date] = maxVal.NewDate)  newVal
        ON d1.Date= newVal.oldDate

Here's an alternative using DENSE_RANK

WITH Cte as 
(Select 
   dv.Date ,
   dv2.val newval,
    DENSE_RANK () OVER (partition by dv.Date order by dv2.Val) RANK
From 
    #datesAndValues dv
    INNER JOIN #datesAndValues dv2
    ON dv.[date] > dv2.[date]
        AND dv.val is null
       and dv2.val is not null
)
UPDATE 
    #datesAndValues
SET 
    val = Cte.newval
FROM 
    #datesAndValues dv
    INNER JOIN Cte 
    ON dv.Date = cte.Date
WHERE
    Cte.RANK = 1