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

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-09-04 : 02:16:51

I have this data...


SELECT x.*
FROM (
	SELECT '2005-03-30 14:14:23.903' AS dtTime, 'S2005CT001' as ItemCode, 30 as QTYin,  NULL as QTYout
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', 10 as QTYin,  NULL as QTYout
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', NULL as QTYin,  15 as QTYout
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', 22 as QTYin,  NULL as QTYout
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', NULL as QTYin,  10 as QTYout
	) AS x

I want to have a running balance by ItemCode with the given data above.

Result shoul be like this...


SELECT x.*
FROM (
	SELECT '2005-03-30 14:14:23.903' AS dtTime, 'S2005CT001' as ItemCode, 30 as QTYin,  NULL as QTYout, 0 as Beg, 30 as Ending
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', 10 as QTYin,  NULL as QTYout,  30, 40 as Ending
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', NULL as QTYin,  15 as QTYout, 40, 25
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', 22 as QTYin,  NULL as QTYout, 25, 47
	union all
	SELECT '2005-04-01 14:14:23.903' AS dtTime, 'S2005CT001', NULL as QTYin,  10 as QTYout, 47, 37
	) AS x

Thanks in advance.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 02:32:04

Do you have primary key in the table ?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-04 : 02:35:11

It is possible, but can you post the design of your source table?

Normally for this sort of thing you would do something like this:
SELECT t.ID, t.QtyIn, t.QtyOut, (SELECT SUM(QtyIn+QtyOut) FROM myTable WHERE ID <= t.ID) as Total
FROM myTable t
ORDER BY ID ASC

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-09-04 : 02:37:50

quote:
Originally posted by khtan

Do you have primary key in the table ?

KH

Yup! sID is my Primary Key

Any approaches besides Timmy's post?

TNX

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 03:02:04

Why don't you tell us this at the first posting? The sID column is not even mentioned?

declare @test table (sid int, dtTime datetime, ItemCode varchar(20), QTYin int, QTYout int)

insert	@test
select	1, '2005-03-30 14:14:23.903', 'S2005CT001', 30, NULL
union all
SELECT 3, '2005-04-01 14:14:23.903', 'S2005CT001', 10, NULL
union all
SELECT 4, '2005-04-01 14:14:23.903', 'S2005CT001', NULL, 15
union all
SELECT 20, '2005-04-01 14:14:23.903', 'S2005CT001', 22, NULL
union all
SELECT 25, '2005-04-01 14:14:23.903', 'S2005CT001', NULL, 10

SELECT	t.*,
	(select isnull(sum(t2.qtyin), 0) - isnull(SUM(t2.qtyout), 0) from @test t2 where t2.sid < t.sid) Beg,
	(select isnull(sum(t2.qtyin), 0) - isnull(SUM(t2.qtyout), 0) from @test t2 where t2.sid <= t.sid) Ending
FROM	@test t

Peter Larsson
Helsingborg, Sweden

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-09-04 : 03:05:41

Any approach? seems my query is very much slow.

Want Philippines to become 1st World COuntry? Go for World War 3...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 11:09:50

see if this helps

http://blogs.conchango.com/jamiethomson/archive/2006/02/28/3001.aspx
http://www.sqlteam.com/item.asp?ItemID=3856

Chirag

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-04 : 11:37:29

this should ideally be done in the front end.
and it's also one of the exceptions to the rule of never using a cursor:
http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-04 : 12:01:26

If you use reports, make use of Running Total feature

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources