>>
>>>>;with cte as (select sum(Amount) as ProgramTotal, [Year]
>>>from ProposedFunding
>>>where tipID = 92.04 and FundType = 'RTA'
>>>GROUP BY [Year])
Completely agree with your recommendations on the blogs for info on CTE and a lot of other stuff. Your posts have always been very helpful and I have the privilege of working with Denis every day at Dow Jones and he definitely know his stuff. It's a lot of fun to be writing C# apps on the front end when Denis is your DBA and can turn you on to alternate ways of approaching some interesting data challenges.
>>>
>>>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
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
