Running balance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Running balance

Author

Topic

noblemfd
Starting Member

38 Posts

Posted - 2013-07-23 : 18:52:13

Hello,
In MSSQL 2000,
I have a table called tblWaybill with the following fields: ItemID, Date1, Qty. Given the opening balance(Obal), I want to calculate the running balance. For the first row, the RunningBal should be, RunningBal = Obal - Qty. Then the subsequent ones should be the RunningBal = RunningBal - Qty. Pls help me out using MSSQL 2000.

CREATE TABLE tblWaybill
ItemID nvarchar(20),
Date1 datetime,
Qty FLOAT(8)

Output:
Obal = 9,000

INSERT INTO tblWaybill
VALUES
('001','20/01/2013',20),
('001','20/02/2013',150),
('001',05/03/2013',30),
('001','06/04/2013',1000)

OUTPUT
Obal = 9,000
ItemID | Date1 | Qty | RunningBal
________________________________________________________
001 | 20/01/2013 | 20 | 8980
001 | 20/02/2013 | 150 | 8830
001 | 05/03/2013 | 30 | 8800
001 | 06/04/2013 | 1000 | 7800

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 19:03:56

Performance will suffer, and you can probably use a cursor too.
Try this first.

DECLARE	@Bal FLOAT

SET	@Bal = 9000

-- SwePeso
SELECT	wb.ItemID,
	wb.Date1,
	wb.Qty,
	(
		SELECT	@Bal - SUM(x.Qty)
		FROM	dbo.tblWaybill AS x
		WHERE	x.ItemID = wb.ItemID
			AND x.Date1 <= wb.Date1
	) AS RunningBal
FROM	dbo.tblWaybill AS wb

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

noblemfd
Starting Member

38 Posts

Posted - 2013-07-24 : 15:19:47

quote:
Originally posted by SwePeso

Performance will suffer, and you can probably use a cursor too.
Try this first.
DECLARE @Bal FLOAT

SET @Bal = 9000

-- SwePeso
SELECT wb.ItemID,
wb.Date1,
wb.Qty,
(
SELECT @Bal - SUM(x.Qty)
FROM dbo.tblWaybill AS x
WHERE x.ItemID = wb.ItemID
AND x.Date1 <= wb.Date1
) AS RunningBal
FROM dbo.tblWaybill AS wb

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thanks so much. Its working, but just for r one set of ItemID. i NEED IT for different itemid. See below

Obal(for ItemID 001): = 9,000
Obal(for ItemID 002): = 8,000
Obal(for ItemID 003): = 10,000
ItemID | Date1 | Qty | RunningBal
________________________________________________________
001 | 20/01/2013 | 20 | 8980
001 | 20/02/2013 | 150 | 8830
001 | 05/03/2013 | 30 | 8800
001 | 06/04/2013 | 1000 | 7800
002 | 20/04/2013 | 20 | 7980
003 | 23/04/2013 | 150 | 9850
003 | 25/04/2013 | 30 | 7950
002 | 26/04/2013 | 1000 | 8850

tHANKS IN ADVANCE

noblemfd
Starting Member

38 Posts

Posted - 2013-07-24 : 15:26:48

[quote]Originally posted by SwePeso

Performance will suffer, and you can probably use a cursor too.
Try this first.

DECLARE	@Bal FLOAT

SET	@Bal = 9000

-- SwePeso
SELECT	wb.ItemID,
	wb.Date1,
	wb.Qty,
	(
		SELECT	@Bal - SUM(x.Qty)
		FROM	dbo.tblWaybill AS x
		WHERE	x.ItemID = wb.ItemID
			AND x.Date1 <= wb.Date1
	) AS RunningBal
FROM	dbo.tblWaybill AS wb

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
[/quote

Correction
Thanks so much. Its working, but just for r one set of ItemID. i NEED IT for different itemid. See below

Obal(for ItemID 001): = 9,000
Obal(for ItemID 002): = 8,000
Obal(for ItemID 003): = 10,000
ItemID | Date1 | Qty | RunningBal
________________________________________________________
001 | 20/01/2013 | 20 | 8980
001 | 20/02/2013 | 150 | 8830
001 | 05/03/2013 | 30 | 8800
001 | 06/04/2013 | 1000 | 7800
002 | 20/04/2013 | 20 | 7980
003 | 23/04/2013 | 150 | 9850
003 | 25/04/2013 | 30 | 9820
002 | 26/04/2013 | 1000 | 6980

tHANKS IN ADVANCE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 15:32:47

[code]-- SwePeso
SELECT wb.ItemID,
wb.Date1,
wb.Qty,
(
SELECT p.Balance - SUM(x.Qty)
FROM dbo.tblWaybill AS x
WHERE x.ItemID = wb.ItemID
AND x.Date1 <= wb.Date1
) AS RunningBal
FROM dbo.tblWaybill AS wb
INNER JOIN (
SELECT '001' AS ItemID, 9000 AS Balance UNION ALL
SELECT '002' AS ItemID, 8000 AS Balance UNION ALL
SELECT '003' AS ItemID, 10000 AS Balance
) AS p ON p.ItemID = wb.ItemID[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

noblemfd
Starting Member

38 Posts

Posted - 2013-07-24 : 15:59:21

quote:

Originally posted by SwePeso

-- SwePeso
SELECT		wb.ItemID,
		wb.Date1,
		wb.Qty,
		(
			SELECT	p.Balance - SUM(x.Qty)
			FROM	dbo.tblWaybill AS x
			WHERE	x.ItemID = wb.ItemID
				AND x.Date1 <= wb.Date1
		) AS RunningBal
FROM		dbo.tblWaybill AS wb
INNER JOIN	(
			SELECT	'001' AS ItemID,  9000 AS Balance UNION ALL
			SELECT	'002' AS ItemID,  8000 AS Balance UNION ALL
			SELECT	'003' AS ItemID, 10000 AS Balance
		) AS p ON p.ItemID = wb.ItemID

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Am very grateful. But the question now is if the ItemID continues FROM '001' TO '050' or more, will I still use UNION ALL. wILL it not be too long.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-25 : 01:56:03

First, create a new helper table to aid tblWaybill table.

CREATE TABLE	tblBalance
		(
			ItemID NVARCHAR(20) NOT NULL,
			Balance FLOAT NOT NULL
		)

INSERT	tblBalance
	(
		ItemID, 
		Balance
	)
VALUES	('001', 9000),
	('002', 8000),
	('003', 10000)

Then you use that table in the query instead.

-- SwePeso
SELECT		wb.ItemID,
		wb.Date1,
		wb.Qty,
		(
			SELECT	b.Balance - SUM(x.Qty)
			FROM	dbo.tblWaybill AS x
			WHERE	x.ItemID = wb.ItemID
				AND x.Date1 <= wb.Date1
		) AS RunningBal
FROM		dbo.tblWaybill AS wb
INNER JOIN	dbo.tblBalance AS b ON b.ItemID = wb.ItemID

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources