时间戳

首页 > 解决方案 > 计算运行总余额

sql-server - 计算运行总余额

问题描述

我有上个月的期末余额，我想根据上个月的期末余额计算每天的流动余额。我尝试的是下面

DECLARE @TestTable TABLE
(
  id int,
  somedate date,
  Credit   INT,
  Debit INT
)

DECLARE @LastMothClosing INT=2000
insert into @TestTable values
(1,     '01/Jan/20',   1000,100),
(2,     '02/Jan/20',   0,0),
(3,     '03/Jan/20',   500,500),
(4,     '04/Jan/20',   1000,200),
(5,     '05/Jan/20',   100,50)

select id,
       somedate,
       Credit,
       Debit,
      (Credit-Debit)+ (SUM(@LastMothClosing) over(order by somedate rows unbounded preceding)) as runningtotal
from @TestTable

我想将前一天的期末余额添加到第二天的贷方金额中，然后它应该从借方金额中减去以获得余额等等。

标签： sql-serversql-server-2012

解决方案

我认为你总结了错误的价值。尝试以下操作：

select id
  , SomeDate
  , Credit
  , Debit
  , @LastMothClosing + sum(Credit-Debit) over (order by somedate rows unbounded preceding) as RunningTotal
from @TestTable;

这使：

id  SomeDate    Credit  Debit   RunningTotal
--------------------------------------------
1   2020-01-01  1000    100     2900
2   2020-01-02  0       0       2900
3   2020-01-03  500     500     2900
4   2020-01-04  1000    200     3700
5   2020-01-05  100     50      3750

推荐阅读