getting numbers from running totals

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)

getting numbers from running totals

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-22 : 07:41:41

sangeeta writes "Hi -

I have seen excellent examples on getting the running total but the problem I am facing is converting running totals into individual quantities. Example

Symbol Running total Trades
MSFT 100
MSFT 500
MSFT 700
MSFT 800
AA 100
AA 300
MSFT 850
AA 400

Given the above table, I would want to connvert this into individual records with their respective traded quantities.

Symbol Trades
MSFT 100
MSFT 400
MSFT 200
MSFT 100
AA 100
AA 200
MSFT 50
AA 100

I will appreciate any help on this.

Thanks a lot.

Sangeeta"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-22 : 09:15:41

assumption : Your running total table must have sequence column like datetime


create table #temp
(
	symbol	varchar(10),
	total	integer,
	seq	int identity(1,1)
)

insert into #temp (symbol, total)
select 'MSFT', 100 union all
select 'MSFT', 500 union all
select 'MSFT', 700 union all
select 'MSFT', 800 union all
select 'AA',   100 union all
select 'AA',   300 union all
select 'MSFT', 850 union all
select 'AA',   400

select 	symbol, total - isnull((select top 1 total from #temp x where x.symbol = t.symbol and x.seq < t.seq order by seq desc), 0)
from	#temp t
order by seq

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 01:37:30

Where do you want to show the data?
Are you using Reports?
If so, then Use Running Total feature of Reports
If you want to do this in query, then it will be time consuming

Why did you store Running Total values?
You should store them as individual records then use Reports to do Running Total

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources