Geek asks and answers

How to use the value from the row above when the given column value is zero?

I have a table of items by date (each row is a new date). I am drawing a value from another column D. I need it to replace 0s. I need the following logic: when D = 0 for this date, use the value in column D from the previous date.

Actually, the truth is, I need to say that when D is 0, use the value from the last date, where D was not 0, but the first will get most of the way.

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

Many thanks.

PS I am using SSMS 2008.

EDIT: At first, I was not very clear. The value I want to change is not a date. I want to change the value in D with the last nonzero value from D based on the date.

+3
source share
5 answers

Maybe the following query might help you. To get the results used OUTER APPLY. Screenshot # 1 shows sample data and sample query output. This query can be written better, but this is something that I could come up with right now.

Hope this 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:

1

+4
source 
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
+1
source

Maybe something like this as part of a script update ...

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

Suppose you want to actually update the data, and not just replace the values ​​for the select query.

0
source

What 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
0
source 
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 completely exclude aggregates,

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
0
source

More articles:


All Articles