---
disqus: kevinwang0510
---
# [SQL 咨詢室系列 - 01] 如何進行數值的累加？
## 前言
今天收到一個如何進行金額累加的問題，問題如下：
> 問：請問我要如何拿到上個月的 carry forward amount + 這個月的 amount?
而問題的截圖中的意思大致如下：
> | Project Name | Transaction Date | Amount (RM) | C/F Amount (RM) |
> | ------------ | ----------------------- | ----------- | --------------- |
> | AAA Project | 2019-11-01 ~ 2019-11-30 | 10,000.00 | 10,000.00 |
> | AAA Project | 2019-12-01 ~ 2019-12-31 | 2,000.00 | 12,000.00 |
> | AAA Project | 2020-01-01 ~ 2020-01-31 | 3,000.00 | 15,000.00 |
> | AAA Project | 2020-02-01 ~ 2020-02-29 | 6,000.00 | 21,000.00 |
## 思路
在看了問題的描述與截圖並了解該問題之後，我的理解是該問題需要針對"**金額**"按"**交易時間**”做”**累加**”計算
另外，在截圖中，針對結果的條件範圍有：
* Project Name
* Account Name
* Transaction Date
而我的想法會是,既然我們已經知道結果的條件範圍，那麼我們就可以先針對條件下手。
所以，我的第一個想法會是先將 "**Transaction Date**" 按照"**年月**"分群，也就是把 "Transaction Date" 給濃縮成 "Transaction Year Month",所以基本的交易結果會是如下：
> | Project Name | Transaction YearMonth | Amount (RM) |
> | ------------ | --------------------- | ----------- |
> | AAA Project | 201911 | 10,000.00 |
> | AAA Project | 201912 | 2,000.00 |
> | AAA Project | 202001 | 3,000.00 |
> | AAA Project | 202002 | 6,000.00 |
## 解法
在將交易資料按照"**年月**"分群後，那麼要進行金額累加就可以簡單的做到，也就是將"**每個交易年月"之前(包含"該交易年月"）** 的金額做加總就可以了，那麼，上程式！
### 程式碼
``` SQL
/* 建立資料表 */
CREATE TABLE #PM_ProjectLedger (
ProjectID VARCHAR(2) NOT NULL,
AccountCode VARCHAR(10) NOT NULL,
CreditAmount DECIMAL(18, 0) NOT NULL DEFAULT(0),
DebitAmount DECIMAL(18, 0) NOT NULL DEFAULT(0),
TransactionDate DATE
);
GO
CREATE TABLE #PM_Project (
ProjectID VARCHAR(2) NOT NULL,
ProjectName VARCHAR(50) NOT NULL
);
GO
/* 寫入專案資料 */
INSERT INTO #PM_Project
(ProjectID
,ProjectName
)
VALUES('90', 'RESORT HOMES SL')
,('0', 'Internal Project');
GO
/* 寫入交易資料 */
INSERT INTO #PM_ProjectLedger
(ProjectID
, AccountCode
, CreditAmount
, DebitAmount
, TransactionDate)
VALUES('90', 'PD-SC-0001', 0, 1000000, CONVERT(DATE, '2019/11/03'))
,('90', 'PD-SC-0001', 0, 12000000, CONVERT(DATE, '2019/11/07'))
,('90', 'PD-SC-0001', 0, 3000000, CONVERT(DATE, '2019/11/12'))
,('90', 'PD-SC-0001', 0, 275000, CONVERT(DATE, '2019/11/23'))
,('90', 'PD-SC-0001', 0, 631000, CONVERT(DATE, '2019/12/02'))
,('90', 'PD-SC-0001', 0, 852000, CONVERT(DATE, '2019/12/03'))
,('90', 'PD-SC-0001', 0, 4650000, CONVERT(DATE, '2019/12/24'))
,('90', 'PD-SC-0001', 0, 12000000, CONVERT(DATE, '2019/12/28'))
,('90', 'PD-SC-0001', 0, 3000000, CONVERT(DATE, '2020/01/15'))
,('90', 'PD-SC-0001', 0, 275000, CONVERT(DATE, '2020/01/17'))
,('90', 'PD-SC-0001', 0, 631000, CONVERT(DATE, '2020/01/31'))
,('90', 'PD-SC-0001', 0, 852000, CONVERT(DATE, '2020/02/04'))
,('90', 'PD-SC-0001', 0, 4650000, CONVERT(DATE, '2020/02/09'))
,('90', 'PD-SC-0001', 0, 12000000, CONVERT(DATE, '2020/02/18'))
,('90', 'PD-SC-0001', 0, 3000000, CONVERT(DATE, '2020/02/21'))
,('90', 'PD-SC-0001', 0, 275000, CONVERT(DATE, '2020/02/29'))
,('90', 'PD-SC-0001', 0, 631000, CONVERT(DATE, '2020/03/05'))
,('90', 'PD-SC-0001', 0, 852000, CONVERT(DATE, '2020/03/11'))
,('90', 'PD-SC-0001', 0, 4650000, CONVERT(DATE, '2020/03/13'))
,('90', 'PD-SC-0001', 0, 12000000, CONVERT(DATE, '2020/03/15'))
,('90', 'PD-SC-0001', 0, 3000000, CONVERT(DATE, '2020/03/16'))
,('90', 'PD-SC-0001', 0, 275000, CONVERT(DATE, '2020/03/21'))
,('90', 'PD-SC-0001', 0, 631000, CONVERT(DATE, '2020/03/27'))
,('90', 'PD-SC-0001', 0, 852000, CONVERT(DATE, '2020/03/28'))
,('90', 'PD-SC-0001', 0, 4650000, CONVERT(DATE, '2020/03/30'))
;
GO
/* 產生 View */
;WITH vw_PM_ProjectLedgerWithProject_AccountCode_Transaction (ProjectName, AccountCode, TransactionYearMonth, Amount)
AS (SELECT ProjectName,
AccountCode,
TransactionYearMonth,
ROUND((SUM(Balance) / 1000000), 2) AS Amount
FROM
(
SELECT p.ProjectName,
pl.AccountCode,
CONVERT(INT, CONVERT(VARCHAR(6), TransactionDate, 112)) AS TransactionYearMonth,
(DebitAmount - CreditAmount) AS Balance
FROM #PM_Project AS p
INNER JOIN #PM_ProjectLedger AS pl
ON p.ProjectID = pl.ProjectID
) AS t
GROUP BY ProjectName,
AccountCode,
TransactionYearMonth
)
SELECT ReportingMonth.ProjectName
, ReportingMonth.TransactionYearMonth
, ReportingMonth.Amount AS [Amount (RM)]
, SUM(PastMonth.Amount) AS [C/F Amount (RM)]
FROM vw_PM_ProjectLedgerWithProject_AccountCode_Transaction AS ReportingMonth
LEFT JOIN vw_PM_ProjectLedgerWithProject_AccountCode_Transaction AS PastMonth
ON ReportingMonth.ProjectName = PastMonth.ProjectName
AND ReportingMonth.AccountCode = PastMonth.AccountCode
AND ReportingMonth.TransactionYearMonth >= PastMonth.TransactionYearMonth
WHERE ReportingMonth.ProjectName = 'RESORT HOMES SL'
AND ReportingMonth.AccountCode = 'PD-SC-0001'
AND ReportingMonth.TransactionYearMonth = 201912
GROUP BY ReportingMonth.ProjectName
, ReportingMonth.TransactionYearMonth
, ReportingMonth.Amount
ORDER BY ReportingMonth.ProjectName
, ReportingMonth.TransactionYearMonth;
```
### 結果
我們可以從下列的表格中看到我們的結果符合一開始的問題，將金額按”**交易時間**”做”**累加**”計算,並且累加的金額也正確的計算出來了。
| Project Name | Transaction Year Month | Amount (RM) | C/F Amount (RM) |
| --------------- | ----------------------- | ----------- | --------------- |
| RESORT HOMES SL | 201911 | 16.280000 | 16.280000 |
| RESORT HOMES SL | 201912 | 18.130000 | 34.410000 |
| RESORT HOMES SL | 202001 | 3.910000 | 38.320000 |
| RESORT HOMES SL | 202002 | 20.780000 | 59.100000 |
| RESORT HOMES SL | 202003 | 27.540000 | 86.640000 |
如果單純指定"**交易年月**"(例如:2019-12)，得到的結果也是正確的。
``` SQL
ReportingMonth.TransactionYearMonth = 201912
```
| Project Name | Transaction Year Month | Amount (RM) | C/F Amount (RM) |
| --------------- | ----------------------- | ----------- | --------------- |
| RESORT HOMES SL | 201912 | 18.130000 | 34.410000 |
## 結語
對於今天的這個"**金額累加**"問題，個人覺得會建議在解決問題前，我們應該先撇開 SQL 的語法，而該思考的方向是先去了解"**累加**"的如何**運作**的，在了解之後，才再回來思考該如何用 SQL 語法將結果給撈出來，希望以上的說明可以幫助到大家了解並學會如何通過 SQL 做"**數值累加**"，當然，數值累加不單單只能針對"**金額**"做，對於客戶的"**點數累加**"、"**哩程數累加**"也都是一樣的喔！
**如果你/妳有任何的想法或是有其他的解決方法，都歡迎底下留言並一起討論喔！**
###### tags: `SQL` `累加` `Cumulative`
Sign in with Wallet
Connect another wallet