Update Record from Previous Row

Question

SQL Server 2008 R2

Have the table below:

Device       | DateOfSample|  Value
=================================
Pump 1 Starts| 2020-07-07|    0
Pump 1 Starts| 2020-07-08|    32
Pump 1 Starts| 2020-07-09|    30
Pump 1 Starts| 2020-07-10|    5
Pump 1 Starts| 2020-07-11|    29
Pump 1 Starts| 2020-07-12|    0
Pump 1 Starts| 2020-07-13|    57
Pump 1 Starts| 2020-07-14|    50
Pump 1 Starts| 2020-07-15|    0
Pump 1 Starts| 2020-07-16|    52

I need to update the value table to have the previous days value if it is 0 so that it looks like this:

Device       | DateOfSample|  Value
=================================
Pump 1 Starts| 2020-07-07|    0
Pump 1 Starts| 2020-07-08|    32
Pump 1 Starts| 2020-07-09|    30
Pump 1 Starts| 2020-07-10|    5
Pump 1 Starts| 2020-07-11|    29
Pump 1 Starts| 2020-07-12|    29
Pump 1 Starts| 2020-07-13|    57
Pump 1 Starts| 2020-07-14|    50
Pump 1 Starts| 2020-07-15|    50
Pump 1 Starts| 2020-07-16|    52

I don't want the first value in the table to change if it is 0, but any other 0 values need to be recorded as the same as the day before.

I've been trying to use Update Case, But I don't seem to be getting anywhere.

UPDATE #MaxValues
    SET Value = CASE
    WHEN m.Value = 0 THEN (
        SELECT m1.Value
        FROM #MaxValues m1
        WHERE m1.DateOfSample = DateAdd (Day, -1, (SELECT m2.DateOfSample FROM #MaxValues m2 WHERE Value = 0)))
    ELSE m.Value
    END
FROM #MaxValues AS m

All I'm getting is an error:

Msg 512, Level 16, State 1, Procedure sp_PumpStartAvg, Line 156 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I get why I'm getting the error, its because there are multiple locations in the table that have 0 and I'm trying to return all of them within the date selection statement. I just don't know how to get around this.

Any advice is greatly appreciated.

Answer 1

Thanks everyone for the help.

I ended up solving the problem by building the original table differently. Thereby eliminating the need to solve the problem at all.

Answer 2

Assuming by "previous row" you mean the "previous date", then a join is the best solution:

update t
    set Value = coalesce(t1.Value, 0)
from t left join
     t tprev
     on tprev.DateofSample = dateadd(day, -1, t.DateofSample);

If by "previous row" you really mean the previous row even when there are gaps, then GMB's solution using lag() -- which requires SQL Server 2012 -- is the better solution.

Answer 3

In SQL Server, think the simplest and most efficient approach is window function and an updatable CTE:

with cte as (
    select value, lag(value) over(partition by devince order by dateOfSample) lag_value
    from mytable 
)
update cte set value = lag_value
where value = 0 and lag_value is not null

Answer 4

You can find previous date using DateTime function (Datediff).Try this query,please:

Update t2
Set t2.Value=t1.Value
FROM Table t1
left join Table t2
on DATEDIFF(dd,t1.DateofSample,t2.DateofSample)=1
and t2.device=t1.device
and t2.Value=0

Answer 5

Try this:

DECLARE @DataSource TABLE
(
    [Device] VARCHAR(16)
   ,[DateOfSample] DATE
   ,[Value] INT
);

INSERT INTO @DataSource ([Device], [DateOfSample], [Value])
VALUES ('Pump 1 Starts', '2020-07-07', 0)
      ,('Pump 1 Starts', '2020-07-08', 32)
      ,('Pump 1 Starts', '2020-07-09', 30)
      ,('Pump 1 Starts', '2020-07-10', 5)
      ,('Pump 1 Starts', '2020-07-11', 29)
      ,('Pump 1 Starts', '2020-07-12', 0)
      ,('Pump 1 Starts', '2020-07-13', 57)
      ,('Pump 1 Starts', '2020-07-14', 50)
      ,('Pump 1 Starts', '2020-07-15', 0)
      ,('Pump 1 Starts', '2020-07-16', 52);

UPDATE DS1
SET [Value] = DS2.[Value]
FROM @DataSource DS1
INNER JOIN @DataSource DS2
    ON DS1.[Device] = DS2.[Device]
    AND DS1.[DateOfSample] = DATEADD(DAY, 1, DS2.[DateOfSample])
WHERe DS1.[Value] = 0


SELECT *
FROM @DataSource
ORDER BY [Device]
        ,[DateOfSample];