Jeff Moden (12/26/2011)

---

fahey.jonathan (12/26/2011)

---

Please notice that the "Females" CTE has been used twice in the outer query, once to list the details and once to provide summary records by job description, something that (to my knowledge...) is not possible with a derived table.

That statement is absolutely correct. However, just like "calling" a view twice in the same query, the CTE that is called twice will also execute twice. You can see that in the execution plan. That might be something to consider in planning for performance. 🙂