>>

>>;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?
>
>~~Bonnie

In this particular case it's the same as derived table. Using CTE makes the query easier to read. For the cons - don't use the same CTE more than once in a query, it can produce performance problems - I found it by experience.

Some good blogs to read about cte:

CTE: Coolest T-SQL Enhancement - Blog by Brad Schulz

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-advent-2011-recap - summary blog by Denis Gobo (cte is day 5), you may find each day to be of interest

And, of course, my own blog
CTE and hierarchical queries