TSQL Challenge 12 - Solution by Greg Holecek

We received a large number of solutions for TSQL Challenge #12 and many of them were very interesting. After reviewing all the solutions, the evaluation team identified the solutions by Greg Holecek, Hodin Matthieu and Syed Mehroz to be the top 3 best solutions.

Solution by Greg Holecek

Here is the solution submitted by Greg Holecek. Greg used a Number Table to generate the required query output.

Generating a Number Table

A Number Table or Calendar Table is one of the most favorite tools of most DBAs and developers. Such a table usually stores a sequence of numbers or dates which can be used to solve a number of common TSQL problems. Greg used such a table to solve the problem presented in TSQL Challenge #12

There are a number of ways to generate a number table. Most common approaches include using a recursive CTE, using ROW_NUMBER() along with a query that returns a large number of rows or using a number of UNION ALL queries to generate digits 0 to 9 and generate the required number of rows using CROSS joins. Greg used the following query to generate a number table needed for solving this problem.(You can see a few examples here)

DECLARE @ten_numbers TABLE(num INT)
INSERT INTO @ten_numbers
SELECT 1 
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 0

SELECT
      1000 * n4.num + 
      100 * n3.num + 
      10 * n2.num + 
      n1.num +
      1 AS num
INTO dbo.numbers
FROM 
      @ten_numbers n1
      CROSS JOIN @ten_numbers n2
      CROSS JOIN @ten_numbers n3
      CROSS JOIN @ten_numbers n4
ORDER BY 1
GO

Step 1: Getting started

Here is the base data used for the challenge.

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)

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

Step 2: Generating the Year-Month Series

Greg's next step was to generate the year-month series for the given period.

SELECT 
	n.num
	, DATEPART(YEAR, DATEADD(MONTH, n.num - 1, FirstYearMonthDate)) * 100
		+ DATEPART(MONTH, DATEADD(MONTH, n.num - 1, FirstYearMonthDate))
		AS YearMonth
FROM 
	dbo.numbers n
	CROSS JOIN
	(  
		SELECT DATEADD(MONTH
			, 12 * ((MIN(YearMonth) / 100) - 1900) + (MIN(YearMonth) % 100)
			, '12/1/1899') AS FirstYearMonthDate 
		FROM @Scores
	) AS user_first_year_month
WHERE
	n.num <= (DATEDIFF(MONTH, FirstYearMonthDate, GETDATE()) + 1)
/*
num         YearMonth
----------- -----------
1           200711
2           200712
3           200801
4           200802
5           200803
6           200804
7           200805
8           200806
9           200807
10          200808
11          200809
12          200810
13          200811
14          200812
15          200901
16          200902
17          200903
18          200904
19          200905
20          200906
21          200907
22          200908
23          200909
24          200910
25          200911
*/

Step 3: Writing the final query

Here is the final version of Greg's Query

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)

SELECT
	all_year_months.YearMonth,
	isnull(scores.Score, last_score.Score) AS Score
FROM( 
	SELECT 
		n.num,
		DATEPART(
			YEAR, DATEADD(MONTH, n.num - 1, FirstYearMonthDate)
		) * 100 + DATEPART(
			MONTH, DATEADD(MONTH, n.num - 1, FirstYearMonthDate)
		) AS YearMonth
	FROM dbo.numbers n
		CROSS JOIN
		(  
			SELECT 
				DATEADD(
					MONTH, 
					12 * ((MIN(YearMonth) / 100) - 1900) 
						+ (MIN(YearMonth) % 100),
					'12/1/1899'
				) AS FirstYearMonthDate 
			FROM @Scores
		) AS user_first_year_month
	WHERE n.num <= (
		DATEDIFF(MONTH, FirstYearMonthDate, GETDATE()) + 1)
) AS all_year_months
LEFT JOIN @scores scores
	ON all_year_months.YearMonth = scores.YearMonth
LEFT JOIN @scores last_score
	ON last_score.YearMonth < all_year_months.YearMonth
	AND NOT EXISTS
	(
		SELECT 1
		FROM @scores middle_score
		WHERE middle_score.YearMonth < all_year_months.YearMonth
			AND middle_score.YearMonth > last_score.YearMonth
	)
ORDER BY all_year_months.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

One of the key points to learn from this solution is the usage of a number table. A number table is unanimously accepted as a very powerful tool and a 'must -have' component of any database. There are a number of problems that a number table can solve or help simplify. A lot of common business problems can be solved in a SET based manner with the help of a number table.

I have given a basic example of parsing a delimited string using a number table here. Over the past few years, I have seen a lot of TSQL problems being solved using a number table in the MSDN forums and I have personally assisted a number of people to solve several problems using a number table.

Have you ever solved a TSQL problem using a number table? Please share your experience with us and the readers by sending us the details of the problem you solved using a number table and we will be very happy to publish it.

Related Posts