>>Since the query is about ranges, groups of records, a recursive query is not the first solution of choice to me. Recursive queries are best applied when you've got to weed through recursive data structures.
>
>One thing, recursion with CTEs has been in SQL Server since 2005. Yes, recursive queries help in querying through recursive/parent-child relationships. (The website www.babybrowns.com has some SQL code running behind the scenes that I wrote for them years ago, to get parent/child relationships on products/brands/etc).
>
>But they also have the benefit of serving as the equivalent of a "perform varying". When I saw Don's example, the notion each subsequent row representing a new set of 4-15 minute segments, I think of the capability in recursive queries to implement that pattern. (Though I had to derive a dummy grouping column to get it to work, so it did wind up a bit complicated).
>
>At a very basic level, if you wanted to create a set of numbers from 1-N, and all you can write is a SQL SELECT statement, you can do something like this (and I'm pretty sure you know this, just more for benefit of anyone who doesn't but would like to know)
>
>

>;WITH NumbersCTE as  
>    (select 1 as Counter
>	    UNION ALL
>		   SELECT Counter + 1 AS Counter from NumbersCTE 
>		            WHERE Counter < 25)
>
>select * from NumbersCTE
>

>
>This can come in handy if someone wants to create a list of Dates. For instance, if I wanted to create a list of Saturdays from today until the end of 2018...
>
>

>;WITH DateListCTE AS
>   (SELECT  cast('7/19/2014' as Date)  AS SaturdayDate
>      union all
>	        SELECT DATEADD(D,7,SaturdayDate) FROM DateListCTE 
>			        WHERE YEAR(DATEADD(D,7,SaturdayDate))  < 2019 )
>SELECT * from DateListCTE  option (maxrecursion 300)   -- since the default # of "loops" is 100
>

>
>I don't think these are better (or worse) than things like correlated subqueries, just different. If a person is going from shop to shop as a contractor, they might very well see code like this, so at the very least understanding these approaches can help.

Wel lets say, when solving the 1st types of problems, recursive queries just are not at the top of my list. Even when creating lists, I'd probably end up with a WHILE construct. Old habits? Perhaps... To me, recursive constructs are just less readable and more difficult to flesh out on how they work. Good to impress other people, but less maintainable, certainly for joe average. Most people in my team speak reasonable SQL as long as you keep away from complex constructs.

Just like using CTE's, recursive queries are the last resort if I cannot solve the problem in any other way. In fact that applies to pretty much anything added to SQL since SQL 2000.

>And yes, correlated subqueries are a great way to solve so many problems of querying for an offset. Many queries that calculate moving averages use them, and they're great.
>
>
>MS has certainly released a good # of new language features. Some are great enhancements over what was done previously (like MERGE, or being able to pass a read-only table as a parameter)....some are certainly nice but not what I would call earth shattering (like GROUPING SETS). And some are certainly big (like the new analytic functions in 2012) that offer great power, but definitely require a learning curve.
>
>I mentioned to Craig B a few weeks ago that there's a pattern with MS development tools. People will come up with an approach to solve a problem...and then years later MS will implement a feature to make solving that problem a bit easier (less code through a new function, etc.). Some will say, "wow, that's great"....others might sometimes say, "OK, nice, but I solved this years ago with older features, and the new function is just different, but not necessarily any better". And both might be right.
>
>
>Oh, and I've been writing SQL since 1992 when Fox 2.0 came out. :)

Roughly the same time, though I started on oracle in 1991 I believe. Then also jumped Fox2.0 and FPW2.6. However, that was not my point. You approach problems from an end to use the new features, which is important to you in a variaty of ways. Since we are backward compatible with SQL2005 (until recently 2000) and we are not making ourliving in using the latest and greatest functions, I approach it from the other end. There is nothing wrong with either as long as you keep an eye on the business sense of it.