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.