Generating Running Balance or Statement of Acount...

Programming & Software Support

Categories

First, We will declare a table and insert values on it.

DECLARE @ledger TABLE (DateEntry DATETIME, [Description] VARCHAR(MAX), Amount MONEY, DEBIT BIT)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2015-12-31 00:00:00.000' AS DateTime), N'Payroll', 0, 30000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-01 12:00:00.000' AS DateTime), N'New Year''s Family Treat', 1, 3000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-01 13:00:00.000' AS DateTime), N'Savings', 1, 10000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-03 07:00:00.000' AS DateTime), N'Tithes and Offering', 1, 3000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-03 12:00:00.000' AS DateTime), N'Lunchdate with Family', 1, 500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-04 06:45:00.000' AS DateTime), N'15 Days Household Allowance', 1, 5000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-04 07:00:00.000' AS DateTime), N'15 Days Daily Expense Allowance', 1, 3000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-04 07:15:00.000' AS DateTime), N'Diesel Full Tank', 1, 1695.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-08 08:00:00.000' AS DateTime), N'Smart', 1, 1500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-08 08:01:00.000' AS DateTime), N'Globe', 1, 500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-08 08:02:00.000' AS DateTime), N'Sky Cable / Internet', 1, 450.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-08 08:03:00.000' AS DateTime), N'Manila Water', 1, 345.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-10 12:00:00.000' AS DateTime), N'Lunchdate with Family', 1, 500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-15 00:00:00.000' AS DateTime), N'Payroll', 0, 30000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-16 08:00:00.000' AS DateTime), N'15 Days Household Allowance', 1, 5000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-16 08:05:00.000' AS DateTime), N'Savings', 1, 10000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-17 07:00:00.000' AS DateTime), N'Tithes and Offering', 1, 3000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-17 12:00:00.000' AS DateTime), N'Lunchdate with Family', 1, 500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-18 07:00:00.000' AS DateTime), N'15 Days Daily Expense Allowance', 1, 3000.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-18 07:10:00.000' AS DateTime), N'Meralco', 1, 4895.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-18 07:15:00.000' AS DateTime), N'Diesel Full Tank', 1, 1600.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-24 12:00:00.000' AS DateTime), N'Lunchdate with Family', 1, 500.0000)
INSERT INTO @ledger ([dateEntry], [Description], [Debit], [Amount]) VALUES (CAST(N'2016-01-25 07:00:00.000' AS DateTime), N'PLDT', 1, 1299.0000)

Now that we have our test table named @ledger, we need to set the start and end date.

DECLARE	@startDate DATETIME = '01/01/2016'
		, @endDate DATETIME = '01/31/2016'

SET @startDate = CAST(CONVERT(char(10), @startDate, 101) + ' 00:00:00' AS datetime)
SET @endDate = CAST(CONVERT(char(10), @endDate, 101) + ' 23:59:59' AS datetime)

Let's declare our temporary ledger table

DECLARE @ledger_temp TABLE (id BIGINT IDENTITY(1,1), DateEntry DATETIME, [Event] VARCHAR(50),
        [Description] VARCHAR(50), DR MONEY, CR MONEY)

Let's get our starting balance

DECLARE @maxDate DATETIME,
        @startingBalance MONEY

SET @maxDate = (
    SELECT MAX(dateEntry)
    FROM @ledger
    WHERE dateEntry < @startDate)

IF @maxDate IS NULL BEGIN
	SET @maxDate = @startDate
END

SELECT	@startingBalance = Amount 
FROM	@ledger
WHERE	DateEntry = @maxDate

IF	@startingBalance IS NULL BEGIN
	SET @startingBalance = 0
END

Let's populate our temporary ledger table

INSERT @ledger_temp
    SELECT DateEntry, [Event], [Description], DR, CR
	FROM	(	SELECT	ts.dateEntry as 'DateEntry',
						(	CASE	ts.Debit
									WHEN 1 THEN 'Payment'
									ELSE 'Income'
							END) AS 'Event',
            ts.[Description] as 'Description',
            (	CASE	ts.debit 
						WHEN 1 THEN ts.amount
				END) AS DR,
            (	CASE	ts.debit
						WHEN 0 THEN ts.amount
				END) AS CR
        FROM	@ledger ts
        WHERE	ts.dateEntry >= @startDate
				AND ts.dateEntry <= @endDate
    ) aa
    ORDER BY DateEntry

Finally, we will now be able to compute our running balance

SELECT	@maxDate  AS 'DateEntry'
        , 'Starting Balance' AS 'Event'
        ,'' AS 'Description'
        ,0 AS DR
        ,0 AS CR
        ,@startingBalance AS RunningTotal
UNION
SELECT	tt1.DateEntry, tt1.[Event], coalesce (tt1.[Description], '') AS DESCRIPTION
		, tt1.DR
		, tt1.CR
		, RunningTotal = SUM (	CASE	WHEN tt2.dr > 0 THEN tt2.dr * -1
										ELSE tt2.cr 
								END) + @startingBalance
FROM	@ledger_temp AS tt1
		JOIN @ledger_temp AS tt2
ON		tt2.dateEntry <= tt1.dateEntry   
GROUP BY	tt1.dateEntry
		, tt1.[Event]
		, tt1.[Description]
		, tt1.dr
		, tt1.cr

Back To Programming & Software Support

Generating Running Balance or Statement of Acount in SQL

Hindi ko inaasahang LIBRE pala ito