So I'm wanting to summarize the balance of the accounts in my business application. My (simplified for our purposes) data model looks like:
--Main account table that holds the single accounts create table account ( id int primary key, descr varchar(50)) --This table holds the budgeted amount for the account. This table also holds the --'actual' adopted amount. The difference is that budgeted is what they thought it --should be, while adopted is what the board accepted. They can be different create table account_amount ( id int primary key, acct_id int foreign key references account (id), amount money, type tinyint) --Each monitary action against an account is contained here. This is the transactional --table create table account_journal ( id int primary key, acct_id int foreign key references account (id), amount money)
I want to create a view to summarize the balance for me, so I've got something like this:
create view account_balance as select a.id, isnull(aaBudgeted.amount,0) as budgeted, isnull(aaAlloted.amount,0) as alloted , sum(isnull(aj.amount,0)) as journaled_activity, , isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance from account a left join acct_amount aaBudgeted on a.id = aaBudgeted.acct_id and aaBudgeted.type = 2 left join acct_amount aaAlloted on a.id = aaAlloted.acct_id and aaAlloted.type = 1 and aaAlloted.is_current = 1 left join acct_journal aj on aj.acct_id = a.id where a.fiscal_year = 2012 group by a.id, aaBudgeted.amount, aaAlloted.amount
My question has to with the:
isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance
line above. Do I want to be referencing an aggregate column twice like this? Is SQL Server 2k (don't get me started) smart enough to NOT sum the values twice? What are my options?