常見的會計報表 SQL 語法處理

在會計報表當中，常常見到以下的報表需求:

前期結存	本期入帳	本期結存
$1000	$500	$1500

但是在 SQL 語法當中，要一次把這個需求處理到位
並不是一件容易的事情。
我想了一個晚上之後，想到了以下的解決方法：

SELECT a.BankID, a.MDate,
SUM(a.InCash) AS 當日現金存入, SUM(a.InCheck) AS 當日支票存入, SUM(a.OutCash) AS 當日現金支出, SUM(a.OutCheck) AS 當日支票支出, SUM(b.SInCash) + SUM(b.SInCheck) - SUM(b.SOutCash) - SUM(b.SOutCheck) AS 前日結存, SUM(b.SInCash) + SUM(b.SInCheck) - SUM(b.SOutCash) - SUM(b.SOutCheck) + SUM(a.InCash) + SUM(a.InCheck) - SUM(a.OutCash) - SUM(a.OutCheck) AS 本日結存, Bank.帳戶摘要, Bank.幣別, Foreign_Cash.CashCode FROM Bank_Detail AS a
INNER JOIN (SELECT BankID, SUM(InCash) AS SInCash, SUM(InCheck) AS SInCheck, SUM(OutCash) AS SOutCash, SUM(OutCheck) AS SOutCheck FROM Bank_Detail WHERE (MDate < @CDate) GROUP BY BankID) AS b ON b.BankID = a.BankID
INNER JOIN Bank ON a.BankID = Bank.ID
INNER JOIN Foreign_Cash ON Bank.ID = Foreign_Cash.ID WHERE (a.MDate = @CDate) GROUP BY a.BankID, a.MDate, b.BankID, Bank.帳戶摘要, Bank.幣別, Foreign_Cash.CashCode

因為我這是個多帳戶，多幣別的系統，所以我還Inner Join了 Bank / Foreign_Cash 的資料表。
來顯示銀行的資訊以及幣別的資訊。
不過最主要就是第一個 Inner Join 裡面的 Select，利用這種方式來做出總和的資料。然後再與本日的合在一起顯示出來。