我有一個期初餘額，我需要按月行計算期末餘額執行總計

我一直在搜尋這個網站和其他人試圖找到答案。我嘗試了各種方法，但無法理解答案，所以就這樣吧。這是我的數據：

我需要的是從每個Acct的begbal開始並添加借方，減去貸方並產生該月的期末餘額（行）。當Acct改變時，拿起新的begbal並重新開始。有了數據，它應該如下所示： [

通過執行以下操作，我可以獲得借方和貸方的總和：

SELECT
pph.Acct,
pph.Year, 
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
SUM(pph.debit+pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM GL_PeriodPostingHistory pph

我想做的是

If Acct <> prevoius Acct

then Sum( begbal+debit-credit)

else Sum(previous endbal+debit-credit) as endbal

我只是不知道怎麼做。

您可以使用FIRST_VALUE視窗函式SUM(pph.debit-pph.credit)來獲得所需的輸出。

SELECT
pph.Acct,
pph.Year, 
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
FIRST_VALUE(pph.begbal) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd)
   + SUM(pph.debit-pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM dbo.GL_PeriodPostingHistory pph;

db小提琴連結。

感謝HandyD提供以 T-SQL 形式編寫的範例數據。

您應該使用遞歸 CTE 來生成執行總計。基本上，您使用 begbal + 貸方 + 借方公式為每個 Acct 組的第 1 行設置 endbal，然後在該帳戶的所有後續行中，您將前一行 endbal 值替換為 begbal 值。

設置：

CREATE TABLE Accounts (
   Acct INT,
   Year INT,
   Prd INT,
   begbal INT,
   debit INT,
   credit INT
)
GO
INSERT INTO Accounts
VALUES (1, 2017, 1, -134, 0, 0),
(1, 2017, 10, 0, 0, 20),
(1, 2017, 11, 0, 0, 186),
(1, 2018, 1, -340, 17, 14),
(1, 2018, 4, 0, 0, 7),
(1, 2018, 6, 0, 0, 33),
(1, 2018, 12, 0, 0, 152),
(1, 2019, 1, -529, 0, 0),
(2, 2014, 1, 1000, 0, 0),
(2, 2015, 1, 1000, 0, 0),
(2, 2015, 5, 0, 0, 950),
(2, 2016, 1, 50, 0, 0),
(2, 2017, 1, 50, 0, 0),
(2, 2018, 1, 50, 0, 0),
(2, 2019, 1, 50, 0, 0)
GO

詢問：

WITH AccountBalances AS (
   SELECT Acct,
       Year,
       Prd,
       begbal,
       debit,
       credit,
       ROW_NUMBER() OVER (PARTITION BY a1.Acct ORDER BY Year, Prd) AS Rn
   FROM Accounts a1
), RunningBalances AS (
   SELECT a1.Acct,
       a1.Year,
       a1.Prd,
       a1.begbal,
       a1.debit,
       a1.credit,
       a1.begbal + SUM(a1.debit + a1.credit) OVER (PARTITION BY a1.Acct ORDER BY a1.Year, a1.Prd) AS endbal,
       a1.rn
   FROM AccountBalances a1
   WHERE Rn = 1
   UNION ALL
   SELECT a1.Acct,
       a1.Year,
       a1.Prd,
       a1.begbal,
       a1.debit,
       a1.credit,
       a2.endbal + SUM(a1.debit + a1.credit) OVER (PARTITION BY a1.Acct ORDER BY a1.Year, a1.Prd) AS endbal,
       a1.rn
   FROM AccountBalances a1
   INNER JOIN RunningBalances a2 ON a2.Acct = a1.Acct AND a2.Rn = a1.Rn - 1
)

SELECT Acct,
   Year,
   Prd,
   begbal,
   debit,
   credit,
   endbal
FROM RunningBalances
ORDER BY Acct, Year, Prd

輸出：

Acct    Year    Prd     begbal  debit   credit  endbal
------------------------------------------------------
1       2017    1       -134    0       0       -134
1       2017    10      0       0       20      -114
1       2017    11      0       0       186     72
1       2018    1       -340    17      14      103
1       2018    4       0       0       7       110
1       2018    6       0       0       33      143
1       2018    12      0       0       152     295
1       2019    1       -529    0       0       295
2       2014    1       1000    0       0       1000
2       2015    1       1000    0       0       1000
2       2015    5       0       0       950     1950
2       2016    1       50      0       0       1950
2       2017    1       50      0       0       1950
2       2018    1       50      0       0       1950
2       2019    1       50      0       0       1950

引用自：https://dba.stackexchange.com/questions/232804