Walter,

Thanks for the reply.

1) You're right, the ANSI format for dates is better. I have a bunch of demo examples where I should update that. Good point.

2) Speaking of ANSI standard, Common Table Expressions "are" part of the ANSI-99 standard. :)

CTEs are available in Oracle, MySQL, MS SQL (since 2005), and other DBs as well.

They are functionally equivalent of derived table expressions. Some developers love them, some don't. As for readable and maintainable, that's a matter of opinion.

Unfortunately, many online sites (and maybe even Wikipedia) refer to CTEs as "temporary named result sets". They're not result sets. It is simply a derived table subquery, placed "above" the outer query that refers to it. It's basically syntax sugar - some like it, some find it too heavy/sweet.

Yes, they are not a replacement for temp tables. If one needs to store a result set and then perform many subsequent operations beyond what a derived table subquery can do, then yes, a temp table will be better.

And yes, the recursive feature in CTEs is more complicated. I've only had to use the recursive feature a small # of times, but it sure came in handy.

3) I mentioned there are several solutions. Yes, as you wrote....this also works, and there will be developers who prefer this.

left outer     join  Purchasing.PurchaseOrderHeader AS POH  
               on Vend.BusinessEntityID = POH.VendorID  and OrderDate BETWEEN '1-1-2008' AND '12-31-2008'

The execution plans are the same.

Some developers prefer to only use the JOIN statement for keys, and some don't. We're talking preference, which IMO needs to be separated from functional benefit.

Thanks...