گزارش صورت حساب در SQL

1CREATE TABLE [Transactions]
2([Row] integer, [Type] integer, [Amount] DECIMAL(18,0))

 1INSERT INTO [Transactions] ([Row], [Type], [Amount])
 2VALUES (1, 1, 1000)
 3
 4INSERT INTO [Transactions] ([Row], [Type], [Amount])
 5VALUES (2, 1, 2000)
 6
 7INSERT INTO [Transactions] ([Row], [Type], [Amount])
 8VALUES (3, -1, 1000)
 9
10INSERT INTO [Transactions] ([Row], [Type], [Amount])
11VALUES (4, 1, 3000)

1DECLARE @SumCredit DECIMAL(18,0);
2SET @SumCredit = 8000;
3
4DECLARE @SumDebit DECIMAL(18,0);
5SET @SumDebit = 2000;
6
7DECLARE @Balance DECIMAL(18,0);
8SET @Balance = 5000;

1SELECT
2	[Row] AS Row,
3	[Type] AS Type,
4	[Amount] AS Amount,
5	((SUM(([Type]*[Amount])*(-1)) OVER (ORDER BY [Row])) + @Balance) AS Balance,
6	((SUM(CASE WHEN [Type] = -1 THEN [Amount] ELSE 0 END) OVER (ORDER BY [Row])) + @SumCredit) AS Credit,
7	((SUM(CASE WHEN [Type] = 1 THEN [Amount] ELSE 0 END) OVER (ORDER BY [Row])) + @SumDebit) AS Debit
8FROM [Transactions]