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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -