SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Running balance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 07/23/2013 :  18:52:13  Show Profile  Reply with Quote
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

Sweden
30249 Posts

Posted - 07/23/2013 :  19:03:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 07/24/2013 :  15:19:47  Show Profile  Reply with Quote
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
Go to Top of Page

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 07/24/2013 :  15:26:48  Show Profile  Reply with Quote
[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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 07/24/2013 :  15:32:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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
Go to Top of Page

noblemfd
Starting Member

Nigeria
38 Posts

Posted - 07/24/2013 :  15:59:21  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 07/25/2013 :  01:56:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000