WHILE loop in SQL

Question

I have two tables: Holdings and Transactions

Holdings data looks like:

06/30/2009, A, 100
06/30/2009, B, 1200
06/30/2009, C, 100
06/30/2009, D, 100

Transactions data looks like:

A, 06/05/2009, 100
B, 06/02/2009, 400
B, 06/13/2009, 400
B, 06/28/2009, 400
C, 06/17/2009, 100
D, 06/30/2009, 100

Ok, so what I need to accomplish is to go through the holdings table and perform a calculation on the transactions where they exist individually.

I am able to able to put all of the transactions into a temp table and use WHILE loop to process them.

declare @count int,
        @loopcount int
declare @tblTransactions TABLE
(
ID int identity(1,1),
trtype varchar(10),
trdate datetime,
trvalue int
)
insert into @tblTransactions
select * from Transactions
select @count=@@rowcount
set @loopcount=1
WHILE @loopcount<=count
BEGIN
     select * from @tblTransactions where ID=@loopcount
     set @loopcount=@loopcount+1
END

Ok, so that is all very well and good but here is the problem: Where the there are multiple transactions for the same holding, trtype column, I need to make a running total of the 'trvalue'.

Not sure how to do it without making a 2nd loop.

help? (and thanks!)

Answer 1

Have you tried a join? Like:

select <yourcalculation>
from holdings h
left join transactions t on h.holdingid = t.holdingid

You can use GROUP BY if you're only interested in an aggregate of the transactions:

select h.name, sum(t.trvalue)
from holdings h
left join transactions t on h.holdingid = t.holdingid
group by h.holdingid

Answer 2

Use a GROUP BY clause to get your summary info and if you are processing any other columns in a complex manner you could use a user defined function for those processes.