Gail... nice explanation! Short, sweet, and to the point.

Sandy... think of a CTE the same as you would any "inline view" or "derived table" except that they are cited before the SELECT instead of in the FROM clause of a SELECT. Big advantage of CTE's is you can reference them more than once in the same SELECT.

Big advantage of a Temp table even over CTE's is that, once built, they can be referenced by multiple queries in the same proc... CTE's can only be referenced by the current query they are built for... just like derived tables.