What query should I use to update Total and Total_previous columns when I update Income or Outcome value for account. Mind that record are sorted by Posting_DataTime column and Transaction_ID is not sorted.

If I update Income value from 10 to 50 for 11112331 (Transaction_ID) then next rows for that account (001) should look like that

CodePudding user response：

Looks like you wish to add up (income - outcome) ordered by the posting_datatime by account_no. A better option instead of updating would be to have this as a "view" instead of updating each time there is a change.

    create table t(account_no varchar(10),trans_id varchar(1000),posting_datatime datetime,Income int, Outcome int);

    insert
      into t
    select '001','11111111','2021-12-01 00:00',100  ,null union all
    select '001','11112331','2021-12-04 04:00',10   ,null union all
    select '021','11111031','2021-12-04 04:23',1200 ,null union all
    select '001','11111231','2021-12-08 07:44',null ,40  union all
    select '001','00011231','2021-12-20 11:59',200  ,null;

    select account_no
           ,trans_id
           ,posting_datatime
           ,income
           ,outcome
           ,sum(isnull(income,0)-isnull(outcome,0)) 
               over(partition by account_no order by posting_datatime) as total
           ,sum(isnull(income,0)-isnull(outcome,0)) 
               over(partition by account_no order by posting_datatime)
                 -(isnull(income,0)-isnull(outcome,0))as total_prev
      from t
    order by posting_datatime;  

link https://sqlize.online/sql/mssql2017/e86a41327cd213441797c775652f9a69/