Calculations Performed on Grouped 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)

Calculations Performed on Grouped Running Totals

Author

Topic

bahamaer
Starting Member

1 Post

Posted - 2008-05-05 : 17:41:02

Currently trying to create a running total on a table that holds account numbers, amounts, and groupings that the amounts are assigned to. I have been able to do this, but now I want to perform calculations on the running totals for each account number. I am only able to do this if there is an account number in the where clause. This is what I have so far. Everything works fine, until I have to perform the calculations beginning with the code in red, from what I see I just need to be able to group all of that by account number, and reset the variables for each account number.

Any suggestions?

DECLARE @totBalance TABLE
(
account_number bigINT,

tbb MONEY,
cbalance money,
rt1 money,
rt2 money,
rt3 money,
calbalance money

)

insert into @totbalance (table1.account_number, tbb,
cbalance, rt1, rt2, rt3)
SELECT table1.account_number, total_beginning_balance,
current_balance,
sum (CASE WHEN table1.transaction_logic_module = 1 THEN table1.amount ELSE 0 END) RunningTotal1,
sum (CASE WHEN table1.transaction_logic_module = 30 THEN table1.amount ELSE 0 END) RunningTotal2,
SUM(CASE WHEN table1.transaction_logic_module = 5 OR
table1.transaction_logic_module = 7 THEN table1.amount ELSE 0 END) RunningTotal5_6

FROM table1 INNER JOIN
table2 ON table1.account_number = table1.account_number AND table1.billing_cycle_date = table2.date_current_statement AND
table1.organization_number = table2.organization_number AND table1.logo = table2.logo

GROUP BY table1.account_number, total_beginning_balance, current_balance

update @totbalance

set @rtotal1 = rt1, @rtotal2 = rt2, @rtotal3 = rt3, @totbegbalance = tbb

declare @balance1 money
set @balance1=0.0

declare @balance2 money
set @balance2=0.0

declare @balance3 money
set @balance3=0.0

set @balance1 = case when @rtotal2 > 0 then @totbegbalance - @rtotal2 end
set @balance2 = case when @rtotal3 > 0 then @balance1 + @rtotal3 end
set @balance3 = case when @rtotal1 > 0 then @balance2 + @rtotal1 end

update @totbalance
set @balance = calbalance = @balance3

Select * from @totbalance
order by account_number

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 21:31:49

quote:

update @totbalance

set @rtotal1 = rt1, @rtotal2 = rt2, @rtotal3 = rt3, @totbegbalance = tbb

What are @rtotal1, @rtotal2,@rtotal3 and @totbegbalance??

You don't have a from clause in your update statement.

Maybe you're trying to do something like this


declare @balance1 money
set @balance1=0.0

declare @balance2 money
set @balance2=0.0

declare @balance3 money
set @balance3=0.0

select @balance1 = case when @rtotal2 > 0 then @totbegbalance - @rtotal2 end,
   @balance2 = case when @rtotal3 > 0 then @balance1 + @rtotal3 end,
   @balance3 = case when @rtotal1 > 0 then @balance2 + @rtotal1 end
From @totbalance
Where .....?

Anyway, can you list your input and your desired output?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources