Alternative to Looping?

[rsx]

Hello,

I am hoping to come up with a clever select statement rather than using a loop.
Problem:
I have a codes that have transactional amounts for each code at various times of the month. So:
Code 1234 Jan 01,2007 $100
Code 1234 Jan 15, 2007 $100
Code 1000 Feb 01, 2007 $200
Code 1000 Feb 10, 2007 $150

Each day I need to re-calculate the balance for each code. So 1234 will be $100 from Jan01-Jan14 and I will have 14 records, on the Jan15 the balance will $200 and I will have 15 records.

With each day being the variable (say starting Jan 01, 2006 up until today so about 750 days of updates). The dates will be in an ordered temp table with an identity.

select @dayCount = 1
select @asAtDate = (select date from tmpTable where ID = @dayCount)

while ( @dayCount <= TotalDateCount )

select
Code
, @asAtDate
, sum(TransactionAmount)
from
TransactionTable
where
TransactionDate <= @asAtDate
group by
Code

select @dayCount = @dayCount + 1
select @asAtDate = (select date from tmpTable where ID =@dayCount)
end

Is there any way to do this with a subquery perhaps and group by each date? I have about 35000 codes to deal with and each needs to have an updated balance everyday.

Thank you in advance for any suggestions.

 

[PDreyer]

This
TransactionDate <= @asAtDate
can be a join to your temp/calendar table instead