>>

>>;with cte as (select sum(Amount)  as ProgramTotal, [Year]
>>from ProposedFunding
>>where tipID = 92.04 and FundType = 'RTA'
>>GROUP BY [Year])
>>
>>select F.*, F.Jan +F.Feb + ... as Total, cte.ProgramTotal
>>from cte LEFT JOIN  [RTA-PmtForecast] F on cte.[Year] = F.[_year] and F.TipID = 92.04
>>order by cte.[Year]

>
>Hi Naomi,
>
>I've never used that "with" construct in T-SQL before, but it looks like something I could use for one of my queries. How efficient is this type of syntax? Do you know of any pros or cons of using it?
>

My understanding is Common Table Expressions (CTE) offer sensational performance gains. A guy I worked with on my most recent project practically spoke in tongues about them. He said he had one monstrous query in a daily batch job that went from something like 40 minutes to 30 seconds when changed to use a CTE. I did not confirm that, that's just what he said.