>>Hi all -
>>I have this query which works OK.
>>

>>select a.TipID, a.AMOUNT*1000 as OldProgram, a.FundType, a.Year, isnull(sum(b.amount),0) as AmountSpent, isnull(c.AMOUNT*1000,0) as NewProgram 
>>from TIP_ApprovedFunding a
>>left join TIP_Ledger b on a.tipid = b.tipid and a.fundtype=b.fundtype and a.year = b.fisyear
>>left join TIP_ProposedFunding c on a.tipid = c.tipid and a.fundtype=c.fundtype and a.year = c.year
>>where a.YEAR= 2013 
>>group by a.TipID,  a.amount, a.FundType, a.YEAR, c.amount
>>order by a.TipID
>>

>>I need to take it one step further and filter the result to only the records where AmountSpent is > NewProgram. My attempts to do this have all failed. Apparently there needs to be a subquery or HAVING clause involved which I don't understand. Can someone help please?
>>Thanks
>
>Are you sure the above works correctly? The reason I am asking is that you may get wrong results with that approach, see my blog post
>http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/aggregates-with-multiple-tables
>
>Anyway, to solve your problem the simplest solution will be using derived table for your current select and adding condition after.
>

>;with cte as (select a.TipID, a.AMOUNT*1000 as OldProgram, a.FundType, a.Year,
> isnull(sum(b.amount),0) as AmountSpent, isnull(c.AMOUNT*1000,0) as NewProgram 
>from TIP_ApprovedFunding a
>left join TIP_Ledger b on a.tipid = b.tipid and a.fundtype=b.fundtype and a.year = b.fisyear
>left join TIP_ProposedFunding c on a.tipid = c.tipid and a.fundtype=c.fundtype and a.year = c.year
>where a.YEAR= 2013 
>group by a.TipID,  a.amount, a.FundType, a.YEAR, c.amount)
>
>SELECT * FROM cte WHERE AmountSpent > NewProgram order by TipID

>
>Or alternatively:
>
>

>select a.TipID, a.AMOUNT*1000 as OldProgram, a.FundType, a.Year, isnull(sum(b.amount),0) as AmountSpent, 
>isnull(c.AMOUNT*1000,0) as NewProgram 
>from TIP_ApprovedFunding a
>left join TIP_Ledger b on a.tipid = b.tipid and a.fundtype=b.fundtype and a.year = b.fisyear
>left join TIP_ProposedFunding c on a.tipid = c.tipid and a.fundtype=c.fundtype and a.year = c.year
>where a.YEAR= 2013 
>group by a.TipID,  a.amount, a.FundType, a.YEAR, c.amount
>
>HAVING isnull(sum(b.amount),0) > isnull(c.AMOUNT*1000,0) order by a.TipID

Thanks Naomi, I haven't had time to get back to this but your insights are helpful (as always).