Dynamically modify the next row of data in the same table using the changed data of the previous row (DEMO)

-- Dynamically modify the next row of data in the same table using the changed data of the previous row (DEMO)

/**//*
Create Table account (
[Month] [datetime] not null prmary key,
[Planfare] [money] Null,
[Putoutfare] [money] Null,
[Planprefare] [money] Null,
[Putoutprefare] [money] Null
) -- Account Table Generation script
*/

Select Identity (INT, 1, 1) as [ID], * into # From account order by [month] ASC

Create Table # temp (
[ID] [int] not null,
[Month] [datetime] not null,
[Planfare] [money] Null,
[Putoutfare] [money] Null,
[Planprefare] [money] Null,
[Putoutprefare] [money] Null
)

Declare @ ID int
Declare M cursor for select [ID] From # order by [ID] ASC
Open m
Fetch next from M into @ ID
While (@ fetch_status = 0)
Begin
Insert into # temp ([ID], [month], planfare, putoutfare, planprefare, putoutprefare)
Select [ID], [month], planfare, putoutfare, planprefare, putoutprefare from # Where [ID] = @ ID

Update #
Set #. planprefare = (A. planfare-A. putoutfare) + (A. planprefare-A. putoutprefare)
From # temp as
Where #. [ID] = A. [ID] + 1
-- Based on the previous row, and then modify it (this row goes forward to the next row, following this loop until the row traversal in the table ends)
Truncate table # temp
Fetch next from M into @ ID
End
Close m
Deallocate m


Update account
Set account. planprefare = A. planprefare
From # As
Where account. [month] = A. [month]


Drop table #
Drop table # temp