enter image description here

select id,date,amount,SUM (Amount)  OVER (PARTITION BY ID ORDER BY DATE desc)  AS runningbalance from sales

I have tried this to reverse running balance going from the most recent to older records. How can I reverse this to go backwards to give me running balance the other way?

CodePudding user response：

If your ID is unique to each row then remove the PARTITION BY ID else it won't sum cumulatively. To reverse the order of the running total simply reverse the ORDER BY of the SUM:

DDL:

declare @sales table (
    ID int,
    Date date,
    Amount int);

insert into @sales
values
    (1, '2020-01-01', 15),
    (2, '2020-01-02', 10),
    (3, '2020-01-03', 5);

DML:

select ID, Date, Amount, 
    sum(Amount) over (order by Date) as RunningBalanceForward, 
    sum(Amount) over (order by Date desc) as RunningBalanceBackwards
from @sales
order by ID

Results:

CodePudding user response：

in addition to the running total, use a row_number to create the sort column and partition it by id order by date desc generating a rowid then order by the rowid

CodePudding user response：

Just to show an example of what the partition is used for.

declare @Sales table (
  ID int identity(1,1) primary key,
  [Date] date,
  Store varchar(30),
  Amount int
  
);

insert into @Sales ([Date], Store, Amount)
values
  ('2020-01-01','A',1), ('2020-01-03','A',1)
, ('2020-01-05','A',1)
, ('2020-01-02','B',10), ('2020-01-04','B',10)
, ('2020-01-06','B',10)
;
    
select Store, [Date], Amount, 
    sum(Amount) over (partition by Store order by [Date] ASC) as RunningTotal, 
    sum(Amount) over (partition by Store order by [Date] DESC) as ReverseRunningTotal
from @Sales
order by Store, [Date] 
GO

Store | Date       | Amount | RunningTotal | ReverseRunningTotal
:---- | :--------- | -----: | -----------: | ------------------:
A     | 2020-01-01 |      1 |            1 |                   3
A     | 2020-01-03 |      1 |            2 |                   2
A     | 2020-01-05 |      1 |            3 |                   1
B     | 2020-01-02 |     10 |           10 |                  30
B     | 2020-01-04 |     10 |           20 |                  20
B     | 2020-01-06 |     10 |           30 |                  10