Update current row with previous row sql server

Question

I have a table with some columns and I want to update current row data with previous adding current row another column value to it. Below is my table

   Date     Type      Temp   Value
2018-04-24  TypeA       0     5
2018-04-25  TypeA       0     12
2018-04-26  TypeA       0     21
2018-04-27  TypeA       0     14
2018-04-30  TypeA       0     16
2018-05-01  TypeA       0.6   18
2018-04-25  TypeB       0     20
2018-04-26  TypeB       0     81
2018-04-27  TypeB       1     42
2018-04-30  TypeB       0     65
2018-05-01  TypeB       0.6   22

I want output to be such that - value column = previous row value column + current row temp.

   Date     Type      Temp   Value
2018-04-24  TypeA       0     5
2018-04-25  TypeA       0     5
2018-04-26  TypeA       0     5
2018-04-27  TypeA       0     5
2018-04-30  TypeA       0     5
2018-05-01  TypeA       0.6   5.6
2018-04-25  TypeB       0     20
2018-04-26  TypeB       0     20
2018-04-27  TypeB       1     21
2018-04-30  TypeB       0     21
2018-05-01  TypeB       0.6   21.6

I have tried to write a query but I am only able to update previous row into current row.

The query I have used is:

UPDATE testTable
SET Value = (select ISNULL(lag(m2.Value,1) over(partition by Type order by Date),testTable.Value) as lag1
                    from testTable m2
                    where m2.Date= testTable.Date
                    and
                    m2.Type= testTable.Type
                   )

I suppose this is Microsoft SQL Server, starting from version 2012, you have LAG to do just that. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017 — Pedro Rodrigues, Oct 15 '18 at 13:22
Show us the query you are currently using so we can make suggestions to modify it. — Jeremy J., Oct 15 '18 at 13:23
@PedroRodrigues I implemented lag but It is updating previous row value into current. So basically on third row I am getting 12 instead of 5 — Deep Sharma, Oct 15 '18 at 13:25
@JeremyJ.I have added the query. I have used rownumber, cte also but couldn't get it to work. — Deep Sharma, Oct 15 '18 at 13:29
Can you identify the primary keys of the table please. This helps with update statements. — Zorkolot, Oct 15 '18 at 13:33
Looks like you want a [running total](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — S3S, Oct 15 '18 at 13:35

John Cappelletti · Accepted Answer · 2018-10-15T13:46:53.593

Here is one approach using a First_Value() and Sum() over()

Example

Declare @YourTable Table ([Date] date,[Type] varchar(50),[Temp] decimal(10,2),[Value] decimal(10,2))
Insert Into @YourTable Values 
 ('2018-04-24','TypeA',0,5)
,('2018-04-25','TypeA',0,12)
,('2018-04-26','TypeA',0,21)
,('2018-04-27','TypeA',0,14)
,('2018-04-30','TypeA',0,16)
,('2018-05-01','TypeA',0.6,18)
,('2018-04-25','TypeB',0,20)
,('2018-04-26','TypeB',0,81)
,('2018-04-27','TypeB',1,42)
,('2018-04-30','TypeB',0,65)
,('2018-05-01','TypeB',0.6,22)

;with cte0 as (
      Select  *
             ,NewValue = FIRST_VALUE(Value) over (Partition By [Type] Order By [Date]) 
                        +Sum(Temp) over (Partition By [Type] Order By [Date]) 
        From  @YourTable
)
Update cte0 set Value = NewValue

The Updated Table

@DeepSharma Happy to help – John Cappelletti Oct 15 '18 at 13:50 — John Cappelletti, Oct 15 '18 at 13:50

Update current row with previous row sql server

1 Answers1