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

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

That is all very well and good but here is the problem: Where 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?

@Christopher Klein: Did it occur to you that writing "<br>" tags at the end of every line might be a bit too complicated for a site like this? :-) There is a simpler way to format code. Use the "format as code button" above the editor. — Tomalak
– Tomalak, Commented May 7, 2009 at 13:24
actually, it did. Alot of times when I try to use the pre tags I end up with error popups, not always. just got into the habit of breaking lines myself. thanks for the cleanup. — Christopher Klein
– Christopher Klein, Commented May 7, 2009 at 13:31
Andomar's solution should help you, but just a quick suggestion... when you're dealing with SQL and you hear yourself think, "I need to go through each record" you should immediately slap yourself on the wrist and instead think, "How can I do this in a set-based manner?" or think in terms of: which rows do I need to include and what do I need to do to them as a group? — Tom H
– Tom H, Commented May 7, 2009 at 13:52
yeah, RBAR (row by agonizing row) is not my friend sometimes. — Christopher Klein
– Christopher Klein, Commented May 7, 2009 at 14:26
@Christopher Klein: I did not say "use <pre> tags". They are actually the wrong thing (apart from a few exceptions). Use the "format as code" button or manually indent your code samples by four spaces. None of this should produce any error messages, though. — Tomalak
– Tomalak, Commented May 7, 2009 at 14:36

Andomar · Accepted Answer · 2009-05-07 13:17:07Z

6

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

answered May 7, 2009 at 13:17

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomalak Over a year ago

+1 Beat me to it. I was in the middle of writing the same thing when that dreaded "a new answer has been posted" popup appeared. :)

littlechris · Accepted Answer · 2009-05-07 13:21:46Z

0

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.

answered May 7, 2009 at 13:21

littlechris

4,20410 gold badges47 silver badges67 bronze badges

Collectives™ on Stack Overflow

WHILE loop in SQL

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related