---
disqus: kevinwang0510
---
# [SQL 咨詢室系列 - 02] 如何進行數值的累加，並對於第一筆加上標記？
## 前言
在繼上一篇["如何進行數值的累加"](https://hackmd.io/@kevinwang0510/B1jVx2ket)的問題之後，我們又接到新的問題，而新的問題中所提供的截圖的意思大致如下：
> | From | To | Transaction | Amount (RM) | C/F Amount (RM) |
> | ---------- | ---------- | --------------- | ----------- | --------------- |
> | 2019-11-01 | 2019-11-30 | Opening Balance | 10,000.00 | 10,000.00 |
> | 2019-12-01 | 2019-12-31 | V | 2,000.00 | 12,000.00 |
> | 2020-01-01 | 2020-01-31 | V | 3,000.00 | 15,000.00 |
> | 2020-02-01 | 2020-02-29 | V | 6,000.00 | 21,000.00 |
> | 2020-03-01 | 2020-03-31 | V | 4,000.00 | 25,000.00 |
## 思路
在看了截圖了解該問題之後，原則上資料分群的邏輯與[前一個問題](https://hackmd.io/@kevinwang0510/B1jVx2ket)相同，只是在於結果的呈現上多了一個"**Transaction**"條件(**第一筆需要顥示"Opening Balance"**)的字樣，之後的只要顥示"**V**"就好。
而我的想法會是：
1. 是先將 "**Transaction Date**" 按照"**年月**"分群，也就是把 "**Transaction Date**" 給濃縮成 "**Transaction Year Month**"
2. 再按照分群後的"**交易年月**"排序並得到每個"**交易年月**"的"**交易順序**""
3. 接著再按照"**交易順序**"進行"**金額累加**"
4. 最後再對於"**交易年月**"處理並得到"**交易年月區間**"
而期望的結果應該要如下表所示：
| From | To | Transaction | Amount (RM) | C/F Amount (RM) |
| ---------- | ---------- | --------------- | ----------- | --------------- |
| 2019-11-01 | 2019-11-30 | Opening Balance | 10,000.00 | 10,000.00 |
| 2019-12-01 | 2019-12-31 | V | 2,000.00 | 12,000.00 |
| 2020-01-01 | 2020-01-31 | V | 3,000.00 | 15,000.00 |
| 2020-02-01 | 2020-02-29 | V | 6,000.00 | 21,000.00 |
| 2020-03-01 | 2020-03-31 | V | 4,000.00 | 25,000.00 |
## 解法
對於此問題的解法，我認為很關鍵的地方有兩個：
1. 排序出交易資料的順序
2. 再按照交易資料的順序進行金額的累加
那麼，讓我們直接上程式吧！
### 程式碼
``` 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 t.[Transaction_From],
t.[Transaction_To],
CASE
WHEN (t.[TransactionSequence] = 1) THEN
'Opening Balance'
ELSE
'V'
END AS [Transaction],
t.Amount AS [ Amount (RM)],
SUM(t.Amount) OVER (ORDER BY t.[TransactionSequence]) AS [C/F Amount (RM)]
FROM
(
SELECT CONVERT(DATE, (CONVERT(VARCHAR(6), ReportingMonth.TransactionYearMonth) + '01')) AS [Transaction_From],
DATEADD(DAY, (-1), DATEADD(MONTH, 1, CONVERT(DATE, (CONVERT(VARCHAR(6), ReportingMonth.TransactionYearMonth) + '01')))) AS [Transaction_To],
ReportingMonth.Amount,
ROW_NUMBER() OVER (PARTITION BY ReportingMonth.ProjectName,
ReportingMonth.AccountCode
ORDER BY ReportingMonth.TransactionYearMonth
) AS [TransactionSequence] -- 這裡的作用是為了 "Transaction" 欄位中需要判斷是不是 "Opening Balance" 而做的預處理
FROM vw_PM_ProjectLedgerWithProject_AccountCode_Transaction AS ReportingMonth
WHERE ReportingMonth.ProjectName = 'RESORT HOMES SL'
AND ReportingMonth.AccountCode = 'PD-SC-0001'
AND ReportingMonth.TransactionYearMonth >= 201911
) AS t;
```
### 結果
| From | To | Transaction | Amount (RM) | C/F Amount (RM) |
| ---------- | ---------- | --------------- | ----------- | --------------- |
| 2019-11-01 | 2019-11-30 | Opening Balance | 16.280000 | 16.280000 |
| 2019-12-01 | 2019-12-31 | V | 18.130000 | 34.410000 |
| 2020-01-01 | 2020-01-31 | V | 3.910000 | 38.320000 |
| 2020-02-01 | 2020-02-29 | V | 20.780000 | 59.100000 |
| 2020-03-01 | 2020-03-31 | V | 27.540000 | 86.640000 |
我們可以從上面的結果表格中看到我們的最後得到的結果符合一開始的問題：
1. 將金額按”**交易時間**”做”**累加**”計算,並且累加的金額也正確的計算出來
2. 第一筆交易顥示"**Opening Balance**"字樣
3. 交易區問要顥示"**交易區間的起-迄日期**"
## 結語
對於新的問題，我們又成功的透過我們對於問題/需求的了解並解決此次新的問題了！那麼，讓我們最後再來回顧一下此解法關鍵的兩個地方：
1. 排序出交易資料的順序
``` SQL
ROW_NUMBER() OVER (PARTITION BY ReportingMonth.ProjectName,
ReportingMonth.AccountCode
ORDER BY ReportingMonth.TransactionYearMonth
)
```
2. 再按照交易資料的順序進行金額的累加
``` SQL
SUM(t.Amount) OVER (ORDER BY t.[TransactionSequence])
```
---
**如果你/妳有任何的想法或是有其他的解決方法，都歡迎底下留言並一起討論喔！**
###### tags: `SQL` `累加` `Cumulative`
Sign in with Wallet
Connect another wallet