>One thing I really hate about CTE is that you can't nest the result of a CTE query as the inner of another query. Its something I frequently do in our framework to build SQL statements dynamically (our Query Designer). One way to avoid that is to get the CTE in an inline function and abstract it away.
>
>

>SELECT * FROM x
>

>
>You can use the result as a subquery
>
>

>SELECT * FROM (SELECT FROM x) as Y WHERE ...
>

>
>but you can't do this
>
>

>SELECT * FROM (WITH CTE (...) as (...) SELECT * FROM CTE) as Y WHERE ...
>

>
>Or use them as arguments in EXISTS(...), IN(...) etc.
>
>Then you'll have to abstract it away
>
>

>SELECT * FROM (SELECT * FROM dbo.MyCTEFunction(...)) as y WHERE ...
>

>
>YMMV,
>
>Walter,

OK with these last part I can see you are really not aware what a CTE is and what you can do with them. Your biggest problem is with the syntax, you are trying to write it as a subquery.

Let's agree to disagree.