---
# System prepended metadata

title: '[SQL 咨詢室系列 - 01] 如何進行數值的累加？'
tags: [Cumulative, 累加, SQL]

---

---
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`