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?
