Select running balance from table credit debit columns
Solution 1
You need to self join table.
CREATE TABLE Test
(
TransDate DATE,
Credit INT,
Debit INT,
);
INSERT INTO Test VALUES
('2014-01-01', 5000, NULL),
('2014-01-07', NULL, 2000),
('2014-01-11', 5000, NULL),
('2014-02-03', 6000, NULL),
('2014-02-06', NULL, 4000),
('2014-02-11', 3000, NULL),
('2014-02-21', NULL, 1000),
('2014-02-28', 2000, NULL),
('2014-03-01', 5000, NULL)
WITH CTE AS
(
SELECT t2.TransDate,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM Test t1
INNER JOIN Test t2
ON t1.TransDate <= t2.TransDate
WHERE t1.DataSource IN (3,4)
GROUP BY t2.TransDate, t2.Credit, t2.Debit
)
SELECT *
FROM CTE
WHERE (TransDate >= '2014/01/11' AND TransDate <= '2014/02/28' )
OUTPUT
TransDate Credit Debit Balance
2014-01-11 5000 (null) 8000
2014-02-03 6000 (null) 14000
2014-02-06 (null) 4000 10000
2014-02-11 3000 (null) 13000
2014-02-21 (null) 1000 12000
2014-02-28 2000 (null) 14000
Solution 2
I would recommend to doing this:
Data Set
CREATE TABLE Test1(
Id int,
TransDate DATE,
Credit INT,
Debit INT
);
INSERT INTO Test1 VALUES
(1, '2014-01-01', 5000, NULL),
(2, '2014-01-07', NULL, 2000),
(3, '2014-01-11', 5000, NULL),
(4, '2014-02-03', 6000, NULL),
(5, '2014-02-06', NULL, 4000),
(6, '2014-02-11', 3000, NULL),
(7, '2014-02-21', NULL, 1000),
(8, '2014-02-28', 2000, NULL),
(9, '2014-03-01', 5000, NULL)
Solution
SELECT TransDate,
Credit,
Debit,
SUM(isnull(Credit,0) - isnull(Debit,0)) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Balance
FROM Test1
order by TransDate
OUTPUT
TransDate Credit Debit Balance
2014-01-01 5000 NULL 5000
2014-01-07 NULL 2000 3000
2014-01-11 5000 NULL 8000
2014-02-03 6000 NULL 14000
2014-02-06 NULL 4000 10000
2014-02-11 3000 NULL 13000
2014-02-21 NULL 1000 12000
2014-02-28 2000 NULL 14000
2014-03-01 5000 NULL 19000
Thank You!
Solution 3
Try this:
Select
x.TransDate,
x.Credit,
x.Debit,
SUM(coalesce(y.credit, 0) - coalesce(y.debit, 0)) AS Balance
FROM Transactions x
INNER JOIN Transasctions y
ON y.TransDate <= x.TransDate
AND Y.DataSource IN (3,4)
WHERE x.DataSource IN (3,4)
GROUP BY
x.TransDate,
x.Credit,
x.Debit
Note that, for large datasets this can get bad real quick... might want to use a cursor or try new 'Windowing' functions.
More here: https://brettwgreen.wordpress.com/2012/10/17/sql-cursors-are-slow-except-when-they-arent/
Smith
Am a software and website developer, i design graphics and icons too!. I love programming, although i only consider myself an intermediate programmer. Hobbies I do alot of googling and ask alot of questions. I love watching movies (Not violent), listening to music (soft, blues etc), reading Others Am very good with my hands (meaning i like to do constructive work with them). If you have a programming problem, pls don't hesitate to ask me for help, i will do the best i can. May you have peace!
Updated on June 14, 2022Comments
-
Smith almost 2 years
I have a SQL Server 2008 table, and I need to select a running balance from it
TransDate Credit Debit Datasource ------------------------------------------ 2014-01-01 5000 NULL 3 2014-01-07 NULL 2000 3 2014-01-11 5000 NULL 3 2014-02-03 6000 NULL 4 2014-02-06 NULL 4000 4 2014-02-11 3000 NULL 4 2014-02-21 NULL 1000 3 2014-02-28 2000 NULL 3 2014-03-01 5000 NULL 3
I tried a correlated query
Select t.TransDate, t.Credit, t.Debit, (Select sum(coalesce(x.credit, 0) - coalesce(x.debit, 0)) From Transactions x WHERE x.DataSource IN (3,4) AND (x.TransDate >= '2014/02/01' AND x.TransDate <= '2014/02/28' ) AND x.TransDate = t.TransDate) Balance From Transactions t
but I get all nulls for the
Balance
.Expected output is
TransDate Credit Debit Balance ------------------------------------------ 2014-01-11 NULL NULL 8000 <- opening balance 2014-02-03 6000 NULL 14000 2014-02-06 NULL 4000 10000 2014-02-11 3000 NULL 13000 2014-02-21 NULL 1000 12000 2014-02-28 2000 NULL 15000 <- closing balance