TSQL Challenges

TSQL Challenge #12 - Solution by Syed Mehroz Alam

In the previous post, we have seen one of the winning solutions of TSQL Challenge #12. Another very interesting winning solution that the evaluation team picked is the one submitted by Syed Mehroz Alam

Solution by Syed Mehroz Alam

Syed submitted a very elegant solution that is much shorter and compact. He used a recursive CTE to produce the required output. If you are not familiar with recursive CTEs, you can find a few examples here.

Sample data

Here is the base data used for the challenge.

YearMonth   Score
----------- -----------
200903      100
200803      95
200802      99
200801      100
200711      100

The Solution

Here is the complete listing of the solution submitted by Syed.

DECLARE @Scores TABLE
(       
    YearMonth    INT,       
    Score        INT
)       
     
INSERT @Scores VALUES(200903, 100)       
INSERT @Scores VALUES(200803, 95)       
INSERT @Scores VALUES(200802, 99)       
INSERT @Scores VALUES(200801 ,100)       
INSERT @Scores VALUES(200711, 100)

;WITH cte AS
(
    SELECT 
		YearMonth, 
		Score
	FROM @Scores
    UNION ALL
    SELECT 
		YearMonth + YearMonth % 100 / 12 * 88 + 1,
		Score
	FROM cte
	WHERE NOT EXISTS ( 
		SELECT 
			s.YearMonth 
		FROM @Scores s 
		WHERE s.YearMonth = (
				cte.YearMonth + 
				cte.YearMonth % 100 / 12 * 88 + 1) 
			)
			and cte.YearMonth < MONTH(GETDATE()) + 
				YEAR(GETDATE())*100
)
SELECT *
FROM cte
ORDER BY YearMonth DESC
/*
YearMonth   Score
----------- -----------
200911      100
200910      100
200909      100
200908      100
200907      100
200906      100
200905      100
200904      100
200903      100
200902      95
200901      95
200812      95
200811      95
200810      95
200809      95
200808      95
200807      95
200806      95
200805      95
200804      95
200803      95
200802      99
200801      100
200712      100
200711      100
*/

Key points to learn

SQL Server 2005 introduced Common Table Expressions (CTEs) which helps reduce the programming complexity of TSQL code to a good extend. What is so special about CTEs is that a CTE can be used to build a recursive query that traverses or generates a tree structure (well, not only limited to the tree structure as you can see from the examples given here).

A lot of common TSQL problems can be solved easily using a recursive CTE. Prior to the introduction of recursive CTEs, recursive stored procedures were used to produce or process hierarchical information, but were limited to 32 levels of recursion. Recursive CTEs allows you to write queries that perform more than 32 levels of recursion and that too, in a SET based fashion.

Have you ever solved a TSQL problem using a recusrive query? We invite you to share your Recursive CTE experience with us.