CODEWITHSUNDEEP
sql-server
Stefan van Roosmalen
Stefan van Roosmalen

Reputation: 23

SQL Server: update table with value from previous record

I have tried several ways using LAG(), ROW_NUMBER() and so on, but I cannot get it working... Please help.

Assume we have this table:

Date         Time         Amount     Balance
---------------------------------------------
20171001     12:44:00     102.00     102.00
20171002     09:32:12      10.00       null
20171002     20:00:00     123.00       null
20171003     07:43:12       5.29       null

My goal is to update the Balance but these records are not ordered in this table.

I have tried to use this code:

with t1 as 
(
    select 
        Date, Time, Amount, Balance, 
        lag(Balance) over (order by Date, Time) Balance_old
    from 
        table1
)
update table1
set Balance = Amount + Balance_old 
where Balance_old is not null

However, this seems to only update 1 record instead of 3 in the above example. Even when I try to do something similar with ROW_NUMBER() then I do not get the results I require.

The results I would like to have are as follows:

Date         Time         Amount     Balance
---------------------------------------------
20171001     12:44:00     102.00     102.00
20171002     09:32:12      10.00     112.00
20171002     20:00:00     123.00     235.00
20171003     07:43:12       5.29     240.29

Please notice: in my situation there is always a record which has a value in Balance. This is the starting point which can be 0 or <>0 (but not null).

Upvotes: 2

Views: 1679

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the approaches is to simply use sum() over() window function.

-- set up 
select *
     into t1
  from (
        select cast('20171001' as date) Date1, cast('12:44:00' as time) Time1, 102.00 Amount, 102.00 Balance union all
        select cast('20171002' as date), cast('09:32:12' as time),  10.00,   null union all
        select cast('20171002' as date), cast('20:00:00' as time), 123.00,   null union all
        select cast('20171003' as date), cast('07:43:12' as time),   5.29,   null
   ) q 


-- UPDATE statement
;with t2 as(
  select date1
       , time1
       , amount
       , balance
       , sum(isnull(balance, amount)) over(order by date1, time1) as balance1
    from t1
)
update t2
  set balance = balance1

The result:

Date1        Time1              Amount      Balance
----------   ----------------   ----------  -------------
2017-10-01   12:44:00.0000000   102.00      102.00
2017-10-02   09:32:12.0000000   10.00       112.00
2017-10-02   20:00:00.0000000   123.00      235.00
2017-10-03   07:43:12.0000000   5.29        240.29

Upvotes: 5

Related Questions