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.
Get notified when a new challenge is available or the evaluation result published either by subscribing to the
RSS feed or subscribing to the Email Notification.