How do I use the value from row above when a given column value is zero?

I have a table of items by date (each row is a new date). I am drawing out a value from another column D. I need it to replace 0s though. I need the 开发者_运维百科following logic: when D=0 for that date, use the value in column D from the date prior.

Actually, truth be told, I need it to say, when D is 0, use the value from the latest date where D was not a 0, but the first will get me most of the way there.

Is there a way to build this logic? Maybe a CTE?

Thank you very much.

PS I'm using SSMS 2008.

EDIT: I wasn't very clear at first. The value I want to change is not the date. I want change the value in D with the latest non-zero value from D, based on date.

May be the following query might help you. It uses the OUTER APPLY to fetch the results. Screenshot #1 shows the sample data and query output against the sample data. This query can be written better but this is what I could come up with right now.

Hope that helps.

SELECT      ITM.Id
        ,   COALESCE(DAT.New_D, ITM.D) AS D
        ,   ITM.DateValue
FROM        dbo.Items   ITM
OUTER APPLY (
                SELECT      
                TOP 1       D   AS New_D
                FROM        dbo.Items DAT
                WHERE       DAT.DateValue   < ITM.DateValue
                AND         DAT.D           <> 0
                AND         ITM.D           = 0
                ORDER BY    DAT.DateValue DESC
            ) DAT

Screenshot #1:

UPDATE t
Set value = SELECT value
              FROM table 
             WHERE date = (SELECT MAX(t1.date)
                             FROM table t1
                            WHERE t1.value != 0
                              AND t1.date < t.date)
 FROM table t
WHERE t.value = 0

You could maybe something like this as part of an update script...

SET myTable.D = (
   SELECT TOP 1 myTable2.D 
   FROM myTable2
   WHERE myTable2.myDateField < myTable.myDateField
   AND myTable2.D != 0
   ORDER BY myTable2.myDateField DESC)

That's assuming that you want to actually update the data though rather than just replace the values for the purpose of a select query.

How about:

SELECT
    i.ID,
    i.DateValue,
    D = CASE WHEN I.D <> 0 THEN I.D ELSE X.D END
FROM
    Items I
    OUTER APPLY (
        SELECT TOP 1 S.D 
        FROM Items S 
        WHERE S.DATEVALUE < I.DATEVALUE AND S.D <> 0 
        ORDER BY S.DATEVALUE DESC
    ) X

SELECT t.id,
    CASE WHEN t.D = 0 THEN t0.D
         ELSE t.D END
FROM table AS t
LEFT JOIN table AS t0
    ON t0.time = 
    (
        SELECT MAX(time) FROM t0
        WHERE t0.time < t.time
        AND t0.D != 0
    )

or if you want to avoid aggregates entirely,

SELECT t.id,
   CASE WHEN t.D = 0 THEN t0.D
       ELSE t.D END
FROM table AS t
LEFT JOIN table AS t0
    ON t0.time < t.time
LEFT JOIN table AS tx
    ON tx.time > t0.time
WHERE t0.D != 0
    AND tx.D != 0
    AND tx.id IS NULL  -- i.e. there isn't any