TSQL Challenge 18 - Generate text formatted month calendars

This is the first time we are coming up with a true calendar puzzle. Brad Schulz from TSQL Challenges Team has come up with a very interesting challenge which is all about generating calendars for given number of months. While the real-world application of this challenge is not very common, this is a good chance to test your date/calendar logic.

Have you got an interesting challenge idea? Send us your challenge idea and we will publish it through TSQL Challenges and you might even get paid!

Sample Data

Mth         Yr
----------- -----------
8           2009
2           1900
10          1959

Your job is to take the above table and generate calendars for the months and years given in the table. A calendar should be generated for each row in the table, using a single query (and no temp tables or table variables)

Expected Output

+-----------------------------+
|        FEBRUARY 1900        |
|=============================|
| Sun Mon Tue Wed Thu Fri Sat |
|-----------------------------|
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28             |
+-----------------------------+
+-----------------------------+
|        OCTOBER 1959         |
|=============================|
| Sun Mon Tue Wed Thu Fri Sat |
|-----------------------------|
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28  29  30  31 |
+-----------------------------+
+-----------------------------+
|         AUGUST 2009         |
|=============================|
| Sun Mon Tue Wed Thu Fri Sat |
|-----------------------------|
|                           1 |
|   2   3   4   5   6   7   8 |
|   9  10  11  12  13  14  15 |
|  16  17  18  19  20  21  22 |
|  23  24  25  26  27  28  29 |
|  30  31                     |
+-----------------------------+

Scripts

Use the following script to generate the sample data

declare @t table (Mth int, Yr int)
insert @t(Mth, Yr) select 8, 2009
insert @t(Mth, Yr) select 2, 1900
insert @t(Mth, Yr) select 10,1959

SELECT * FROM @t

Rules

This challenge demonstrates skill in using Date Functions, Grouping, Pivoting, Numbers Table, Recursion and CTEs.

1. The resulting output is a single 31-character column called Calendar
2. The Month should be uppercase and should be rendered in the language that is set at runtime
3. The Month and Year are centered
4. The Day-Of-The-Week names are the first 3 letters of the days of the week, rendered in the language that is set at runtime. Sunday must be the first column
5. The calendars must be sorted in order
6. The output must be unchanged regardless of the SET DATEFIRST setting

Here's a sample of the same output with SET LANGUAGE FRENCH:

+-----------------------------+
|        FÉVRIER 1900         |
|=============================|
| dim lun mar mer jeu ven sam |
|-----------------------------|
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28             |
+-----------------------------+
+-----------------------------+
|        OCTOBRE 1959         |
|=============================|
| dim lun mar mer jeu ven sam |
|-----------------------------|
|                   1   2   3 |
|   4   5   6   7   8   9  10 |
|  11  12  13  14  15  16  17 |
|  18  19  20  21  22  23  24 |
|  25  26  27  28  29  30  31 |
+-----------------------------+
+-----------------------------+
|          AOÛT 2009          |
|=============================|
| dim lun mar mer jeu ven sam |
|-----------------------------|
|                           1 |
|   2   3   4   5   6   7   8 |
|   9  10  11  12  13  14  15 |
|  16  17  18  19  20  21  22 |
|  23  24  25  26  27  28  29 |
|  30  31                     |
+-----------------------------+

Notes

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Last date to submit your entries: Dec 14 2009 Midnight GMT
4. Use this forum for any questions related to TSQL Challenge #18

Syntax Bookmark of the Day! – IDENTITY Functions and commands
	Quick reference for SQL Server IDENTITY related TSQL Commands and functions http://syntaxhelp.com/sqlserver/identity

About the Author

TSQL Challenge 13 – Solution by Neeraj Mathur and other TSQL Heros

Introducing new testing levels with complex (tricky) data

In the previous post, I had mentioned about the new (improved) evaluation process for the challenges. Brad Schulz is working very hard on leading the process to build the data (which I call ‘tricky and interesting data’ but he prefers to call ‘data that presents exceptional cases’) for the better evaluation of the future challenges.

Brad spent many hours working on the data for challenge 13 and came up with a very interesting set of data on which we decided to run the ‘fire-test’ of all the solutions. After running the submissions on Brad’s data, we analyzed the results and saw those SQL Heros who always tend to write code that handles all type of exceptional cases that can come in a serious real-world database application.

So, who are those heros? Here is the list:

• Randall S. Derby
• Leonid Koyfman
• Tobias Ortmann
• Steve Howard

What is so special about the solutions of these people? Well it is the way they handle unexpected data. Their solutions work with the sample data provided. In addition, they have taken care to handle a number of odd cases. We see this as something that comes naturally from their experience in serious real-world database programming. We will soon publish their solutions in this column.

We are trying to make all the testing data publicly available so that all of you can examine it and run your solutions through it.

Solution by Neeraj Mathur

Neeraj built his solution using two CTEs out of which one is a RECURSIVE CTE. If you are not familiar with recursive CTEs, this article might help.

;WITH CTE1 AS
(
	SELECT 
		batchnumber,
		invoicenumber,
		COUNT(*) AS cn,
		ROW_NUMBER() OVER(ORDER BY batchnumber,invoicenumber) AS Rn 
		FROM @t 
		GROUP BY batchnumber,invoicenumber
)
SELECT * FROM CTE1

This produces the following output.

batchnumber invoicenumber cn Rn
----------- ------------- -- --
10000001    20001         4  1
10000001    20002         3  2
10000001    20003         2  3
10000001    20004         6  4
10000001    20005         8  5
10000001    20006         1  6
10000002    20007         3  7
10000002    20008         3  8

He then wrote a recursive CTE taking a reference to the previous CTE.

CTE2 AS(
    SELECT 
		batchnumber,
		invoicenumber,
		rn,
		Runingcnt=cn 
	FROM CTE1 WHERE rn=1
    UNION ALL
    SELECT 
		C1.batchnumber,
		C1.invoicenumber,
		C1.rn,
		CASE 
			WHEN C1.batchnumber=C2.batchnumber 
				THEN C1.cn+c2.Runingcnt 
			ELSE C1.cn 
		END AS Runingcnt
     FROM CTE2 C2 JOIN CTE1 C1 ON c1.rn=c2.rn+1
)
SELECT * FROM CTE2

Here is the output of the recursive CTE.

batchnumber invoicenumber rn Runingcnt
----------- ------------- -- ---------
10000001    20001         1  4
10000001    20002         2  7
10000001    20003         3  9
10000001    20004         4  15
10000001    20005         5  23
10000001    20006         6  24
10000002    20007         7  3
10000002    20008         8  6

And then he moved on to the final version of the solution.

SELECT 
	T.*,
	round(C2.RuningCnt/10,0)+1 as [SET] 
FROM @t T 
JOIN CTE2 C2 ON C2.batchnumber=T.batchnumber 
	AND c2.invoicenumber=T.invoicenumber  
OPTION (maxrecursion 0)

The final solution

Here is the listing of the final version of Neeraj’s solution.

DECLARE @t TABLE (
	InvID INT IDENTITY,
	BatchNumber INT,
	InvoiceNumber INT, 
	VisitDate DATETIME,
	Amount MONEY )
INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-03',50.00
;WITH CTE1 AS
(
	SELECT 
		batchnumber,
		invoicenumber,
		COUNT(*) AS cn,
		ROW_NUMBER() OVER(ORDER BY batchnumber,invoicenumber) AS Rn 
		FROM @t 
		GROUP BY batchnumber,invoicenumber
),CTE2 AS(
    SELECT 
		batchnumber,
		invoicenumber,
		rn,
		Runingcnt=cn 
	FROM CTE1 WHERE rn=1
    UNION ALL
    SELECT 
		C1.batchnumber,
		C1.invoicenumber,
		C1.rn,
		CASE 
			WHEN C1.batchnumber=C2.batchnumber 
				THEN C1.cn+c2.Runingcnt 
			ELSE C1.cn 
		END AS Runingcnt
     FROM CTE2 C2 JOIN CTE1 C1 ON c1.rn=c2.rn+1
)
SELECT 
	T.*,
	round(C2.RuningCnt/10,0)+1 as [SET] 
FROM @t T 
JOIN CTE2 C2 ON C2.batchnumber=T.batchnumber 
	AND c2.invoicenumber=T.invoicenumber  
OPTION (maxrecursion 0) 
/*
InvID BatchNumber InvoiceNumber VisitDate  Amount SET
----- ----------- ------------- ---------- ------ ---
1     10000001    20001         2009-01-01 50.00  1
2     10000001    20001         2009-01-02 50.00  1
3     10000001    20001         2009-01-03 50.00  1
4     10000001    20001         2009-01-04 50.00  1
5     10000001    20002         2009-01-01 50.00  1
6     10000001    20002         2009-01-02 50.00  1
7     10000001    20002         2009-01-03 50.00  1
8     10000001    20003         2009-01-01 50.00  1
9     10000001    20003         2009-01-02 50.00  1
10    10000001    20004         2009-01-01 50.00  2
11    10000001    20004         2009-01-02 50.00  2
12    10000001    20004         2009-01-03 50.00  2
13    10000001    20004         2009-01-04 50.00  2
14    10000001    20004         2009-01-05 50.00  2
15    10000001    20004         2009-01-06 50.00  2
16    10000001    20005         2009-01-01 50.00  3
17    10000001    20005         2009-01-02 50.00  3
18    10000001    20005         2009-01-13 50.00  3
19    10000001    20005         2009-01-14 50.00  3
20    10000001    20005         2009-01-15 50.00  3
21    10000001    20005         2009-01-06 50.00  3
22    10000001    20005         2009-01-07 50.00  3
23    10000001    20005         2009-01-08 50.00  3
24    10000001    20006         2009-01-01 50.00  3
25    10000002    20007         2009-01-01 50.00  1
26    10000002    20007         2009-01-02 50.00  1
27    10000002    20007         2009-01-03 50.00  1
28    10000002    20008         2009-01-01 50.00  1
29    10000002    20008         2009-01-02 50.00  1
30    10000002    20008         2009-01-03 50.00  1
*/

Please join me to congratulate Neeraj for this interesting solution.

Summary

SQL Server 2005 introduced a number of interesting programmability features and we are using many of them extensively. ROW_NUMBER and other window functions, CTEs and RECURSIVE CTEs are very powerful programmability features added in SQL Sever 2005 which help to solve a number of common business problems easily.

TSQL Challenge 13 – Solution by Isham Hamin

Better and Stronger Evaluation Process

One of the challenges we, The TSQL Challenge Team has, is to come up with a robust evaluation process. As we dig deeper and deeper into it, we realize that it is not as easy as we thought. We need to come up with better and stronger evaluation process to make sure that each solution is tested from different angles.

We could not do an extensive set of tests on the solutions of the previous challenges. All the solutions were tested using the sample data provided. From the next challenge onwards, we are trying to come up with a three level evaluation process that involves the following.

1. Basic Testing – This test is done on the sample data provided with the challenge. This test will filter out solutions that do not produce the expected result.
2. Logic Testing – Solutions that pass the Basic Testing will be moved to Logic Testing. During this testing phase, the solutions will be tested against a tricky set of data that validates the accuracy of the solution under exceptional circumstances.
3. Load Testing – Solutions that pass Logic testing will be moved to the final testing phase. During this testing phase, the solutions will be tested against a large volume of data and performance statistics will be collected and analyzed.

We are trying hard to make sure that we will be able to provide you with the test data for all the three levels of testing, at the time of publishing a challenge. However, generating this type of data (especially for Logic Testing) needs a lot of efforts and I would like to invite volunteers from the SQL Server community to come forward and help the evaluation team. If you would like to lend us a hand, please let us know.

Solution by Isham Hamin

Isham’s solution (TSQL Challenge #13) starts with a simple query that uses SQL Server Window Function, ROW_NUMBER, to generate a unique sequence number. If you are not familiar with the different Window Functions, this article might help you. He generates two sequence numbers, one that resets with each batch and the other that resets with each invoice.

SELECT 
	BatchNumber,
	InvoiceNumber,
	RunningNbr	= ROW_NUMBER() 
		OVER (PARTITION BY BatchNumber ORDER BY InvID),
	SelectedRow = ROW_NUMBER() 
		OVER (PARTITION BY BatchNumber,InvoiceNumber 
			ORDER BY InvID DESC)
FROM @t

The above query produces the following output.

BatchNumber InvoiceNumber RunningNbr SelectedRow
----------- ------------- ---------- -----------
..... removed for brevity ......................
10000001    20005         19         5
10000001    20005         20         4
10000001    20005         21         3
10000001    20005         22         2 
10000001    20005         23         1<=
10000001    20006         24 <=      1<=
10000002    20007         1          3
10000002    20007         2          2
10000002    20007         3          1<=
10000002    20008         4          3
10000002    20008         5          2
10000002    20008         6          1<=

On top of this result, Isham wrote an outer query that tried to categorizes invoices into different sets.

SELECT	BatchNumber,
		InvoiceNumber,
		SetNbr = FLOOR(RunningNbr/10)+1
FROM (
	SELECT 
		BatchNumber,
		InvoiceNumber,
		RunningNbr	= ROW_NUMBER() 
			OVER (PARTITION BY BatchNumber ORDER BY InvID),
		SelectedRow = ROW_NUMBER() 
			OVER (PARTITION BY BatchNumber,InvoiceNumber 
				ORDER BY InvID DESC)
	FROM @t
) t1
WHERE SelectedRow = 1

Here is the output of the above query.

BatchNumber InvoiceNumber SetNbr
----------- ------------- ------
10000001    20001         1
10000001    20002         1
10000001    20003         1
10000001    20004         2
10000001    20005         3
10000001    20006         3
10000002    20007         1
10000002    20008         1

And then, he put the previous query into a CTE and wrote the final version of the query, which produces the required output.

;WITH Inv_CTE AS
(
	SELECT	BatchNumber,
			InvoiceNumber,
			SetNbr = FLOOR(RunningNbr/10)+1
	FROM (
		SELECT 
			BatchNumber,
			InvoiceNumber,
			RunningNbr	= ROW_NUMBER() 
				OVER (PARTITION BY BatchNumber ORDER BY InvID),
			SelectedRow = ROW_NUMBER() 
				OVER (PARTITION BY BatchNumber,InvoiceNumber 
					ORDER BY InvID DESC)
		FROM @t
	) t1
	WHERE SelectedRow = 1
)
SELECT	t2.InvID,
		t1.BatchNumber,
		t1.InvoiceNumber,
		t2.VisitDate,
		t2.Amount,
		t1.SetNbr
FROM Inv_CTE t1
INNER JOIN @t t2 ON t2.BatchNumber = t1.BatchNumber 
	AND t2.InvoiceNumber = t1.InvoiceNumber

The Final Solution

Here is the complete listing of Isham’s soution.

DECLARE @t TABLE
( 
	InvID			INT IDENTITY, 
	BatchNumber		INT, 
	InvoiceNumber	INT,  
	VisitDate		DATETIME, 
	Amount			MONEY
) 
INSERT INTO @t (BatchNumber, InvoiceNumber, VisitDate, Amount) 
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL 
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL 
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL 
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL 
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL 
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL 
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL 
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL 
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL 
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL  
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL 
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL 
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL 
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL 
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL 
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL 
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL 
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL 
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL 
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL 
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL 
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL 
SELECT 10000002,20008,'2009-01-03',50.00

;WITH Inv_CTE AS
(
	SELECT	BatchNumber,
			InvoiceNumber,
			SetNbr = FLOOR(RunningNbr/10)+1
	FROM (
		SELECT 
			BatchNumber,
			InvoiceNumber,
			RunningNbr	= ROW_NUMBER() 
				OVER (PARTITION BY BatchNumber ORDER BY InvID),
			SelectedRow = ROW_NUMBER() 
				OVER (PARTITION BY BatchNumber,InvoiceNumber 
					ORDER BY InvID DESC)
		FROM @t
	) t1
	WHERE SelectedRow = 1
)
SELECT	t2.InvID,
		t1.BatchNumber,
		t1.InvoiceNumber,
		t2.VisitDate,
		t2.Amount,
		t1.SetNbr
FROM Inv_CTE t1
INNER JOIN @t t2 ON t2.BatchNumber = t1.BatchNumber 
	AND t2.InvoiceNumber = t1.InvoiceNumber

/*
InvID BatchNumber InvoiceNumber VisitDate  SetNbr
----- ----------- ------------- ---------- ------
1     10000001    20001         2009-01-01 1
2     10000001    20001         2009-01-02 1
3     10000001    20001         2009-01-03 1
4     10000001    20001         2009-01-04 1
5     10000001    20002         2009-01-01 1
6     10000001    20002         2009-01-02 1
7     10000001    20002         2009-01-03 1
8     10000001    20003         2009-01-01 1
9     10000001    20003         2009-01-02 1
10    10000001    20004         2009-01-01 2
11    10000001    20004         2009-01-02 2
12    10000001    20004         2009-01-03 2
13    10000001    20004         2009-01-04 2
14    10000001    20004         2009-01-05 2
15    10000001    20004         2009-01-06 2
16    10000001    20005         2009-01-01 3
17    10000001    20005         2009-01-02 3
18    10000001    20005         2009-01-13 3
19    10000001    20005         2009-01-14 3
20    10000001    20005         2009-01-15 3
21    10000001    20005         2009-01-06 3
22    10000001    20005         2009-01-07 3
23    10000001    20005         2009-01-08 3
24    10000001    20006         2009-01-01 3
25    10000002    20007         2009-01-01 1
26    10000002    20007         2009-01-02 1
27    10000002    20007         2009-01-03 1
28    10000002    20008         2009-01-01 1
29    10000002    20008         2009-01-02 1
30    10000002    20008         2009-01-03 1
*/

Please Join me to congratulate Isham Hamin!

TSQL Challenge 13 – Winning Solution by Jim Carter

We had a glitch in one of the automatic scripts that we use to evaluate the solutions and had to re-evaluate the solutions last night. After spending the whole night (and unlimited cups of coffee) evaluating and reviewing the different solutions, we have the final list of winning solutions.

 

Thank you Brad, Rui and David for your help in re-doing the evaluation.

Solution by Jim Carter

I found the solution by Jim Carter to be very simple and elegant. He used a CTE that uses ROW_NUMBER() and PARTITION BY along with an elegant logic to make sure that the invoices don’t break apart into different sets. Please see TSQL Challenge #13 for problem description, sample data and expected results.

Getting Started

Jim started with a simple CTE that produced a new column using ROW_NUMBER() which resets after each batch is completed.

 SELECT 
    BatchNumber, 
    InvoiceNumber, 
    VisitDate,
    (
		ROW_NUMBER() OVER 
		(PARTITION BY BatchNumber ORDER BY InvoiceNumber) 
	) AS [Working_Set]  
  FROM @t 
)
SELECT * FROM mycte

The above code produces the following output.

BatchNumber InvoiceNumber VisitDate   Working_Set
----------- ------------- ----------  -----------
10000001    20001         2009-01-01  1
10000001    20001         2009-01-02  2
10000001    20001         2009-01-03  3
10000001    20001         2009-01-04  4
10000001    20002         2009-01-01  5
10000001    20002         2009-01-02  6
10000001    20002         2009-01-03  7
10000001    20003         2009-01-01  8
10000001    20003         2009-01-02  9
10000001    20004         2009-01-01  10
10000001    20004         2009-01-02  11
10000001    20004         2009-01-03  12
........    .....         ...continued.. 

Next, he added the code to group the rows into sets of 10 rows. This is done by doing a division by 10 on the new column working_set (taking advantage of the SQL behavior that always returns an INT when an arithmetic operation of two INT values takes place).

;WITH MyCTE AS (
  SELECT 
    BatchNumber, 
    InvoiceNumber, 
    VisitDate,
    (
		(
			ROW_NUMBER() OVER 
			(PARTITION BY BatchNumber ORDER BY InvoiceNumber) / 10
		) + 1
	) AS [Working_Set]  
  FROM @t 
)
SELECT * FROM MyCTE 

It produces the following output (Note the usage of division by 10 which produces an INT number - 0 for the first 9 rows, and 1 for the next 10 rows etc). Adding +1 to the result of the division helps to group the rows into the correct sets.

BatchNumber InvoiceNumber VisitDate   Working_Set
----------- ------------- ----------  -----------
10000001    20001         2009-01-01  1
10000001    20001         2009-01-02  1
10000001    20001         2009-01-03  1
10000001    20001         2009-01-04  1
10000001    20002         2009-01-01  1
10000001    20002         2009-01-02  1
10000001    20002         2009-01-03  1
10000001    20003         2009-01-01  1
10000001    20003         2009-01-02  1
10000001    20004         2009-01-01  2
10000001    20004         2009-01-02  2
........    .....         continued....

Good so far! However, there is one more challenge. According to the problem description, an invoice should not be broken into two different sets. The above query does not implement this rule. If you examine the result set, you could see the following (which is wrong)

BatchNumber InvoiceNumber VisitDate   Working_Set
----------- ------------- ----------  -----------
........ removed for brevity ....................
10000001    20005         2009-01-01  2
10000001    20005         2009-01-02  2
10000001    20005         2009-01-13  2
10000001    20005         2009-01-14  2
10000001    20005         2009-01-15  3 <= Problem!
10000001    20005         2009-01-06  3
10000001    20005         2009-01-07  3
10000001    20005         2009-01-08  3
........ removed for brevity ....................

Note that invoice “20005” is broken into two sets (set 2 and set 3). So the next challenge is to fix this behavior. All the rows of invoice “20005” should be either in set 2 or in set 3. At the same time, one set cannot have more than 10 rows.

Jim came up with the following logic to fix this problem (which produces the expected result).

;WITH MyCTE AS (
  SELECT 
    Amount, 
    ROW_NUMBER() OVER (ORDER BY BatchNumber, InvoiceNumber) AS InvID,
    BatchNumber, 
    InvoiceNumber, 
    VisitDate,
    (
		(
			ROW_NUMBER() OVER 
			(PARTITION BY BatchNumber ORDER BY InvoiceNumber) / 10
		) + 1
	) AS [Working_Set]  
  FROM @t 
)
SELECT
  MyCTE.InvID,
  MyCTE.BatchNumber,
  MyCTE.InvoiceNumber,
  MyCTE.VisitDate,
  MyCTE.Amount,
  t2.[Set]
FROM MyCTE 
INNER JOIN ( 
    SELECT 
      InvoiceNumber, 
      MAX([Working_Set]) AS [Set], 
      COUNT(InvID) AS number_in_file 
    FROM MyCTE 
    GROUP BY InvoiceNumber
) t2 ON MyCTE.InvoiceNumber = t2.InvoiceNumber

The final Solution

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

DECLARE @t TABLE (
    InvID INT IDENTITY,
    BatchNumber INT,
    InvoiceNumber INT,
    VisitDate DATETIME,
    Amount MONEY )
    
INSERT INTO @t(BatchNumber, InvoiceNumber, VisitDate, Amount)
SELECT 10000001,20001,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20001,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20002,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20003,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-03',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-04',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-05',50.00 UNION ALL
SELECT 10000001,20004,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-01',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-02',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-13',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-14',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-15',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-06',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-07',50.00 UNION ALL
SELECT 10000001,20005,'2009-01-08',50.00 UNION ALL
SELECT 10000001,20006,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20007,'2009-01-03',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-01',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-02',50.00 UNION ALL
SELECT 10000002,20008,'2009-01-03',50.00

;WITH MyCTE AS (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY BatchNumber, InvoiceNumber) AS InvID,
    BatchNumber, 
    InvoiceNumber, 
    VisitDate,
    (
		(
			ROW_NUMBER() OVER 
			(PARTITION BY BatchNumber ORDER BY InvoiceNumber) / 10
		) + 1
	) AS [Working_Set]  
  FROM @t 
)
SELECT
  MyCTE.InvID,
  MyCTE.BatchNumber,
  MyCTE.InvoiceNumber,
  MyCTE.VisitDate,
  t2.[Set]
FROM MyCTE 
INNER JOIN ( 
    SELECT 
      InvoiceNumber, 
      MAX([Working_Set]) AS [Set], 
      COUNT(InvID) AS number_in_file 
    FROM MyCTE 
    GROUP BY InvoiceNumber
) t2 ON MyCTE.InvoiceNumber = t2.InvoiceNumber

/*
InvID BatchNumber InvoiceNumber VisitDate  Set
----- ----------- ------------- ---------- ----
1     10000001    20001         2009-01-01 1
2     10000001    20001         2009-01-02 1
3     10000001    20001         2009-01-03 1
4     10000001    20001         2009-01-04 1
5     10000001    20002         2009-01-01 1
6     10000001    20002         2009-01-02 1
7     10000001    20002         2009-01-03 1
8     10000001    20003         2009-01-01 1
9     10000001    20003         2009-01-02 1
10    10000001    20004         2009-01-01 2
11    10000001    20004         2009-01-02 2
12    10000001    20004         2009-01-03 2
13    10000001    20004         2009-01-04 2
14    10000001    20004         2009-01-05 2
15    10000001    20004         2009-01-06 2
16    10000001    20005         2009-01-01 3
17    10000001    20005         2009-01-02 3
18    10000001    20005         2009-01-13 3
19    10000001    20005         2009-01-14 3
20    10000001    20005         2009-01-15 3
21    10000001    20005         2009-01-06 3
22    10000001    20005         2009-01-07 3
23    10000001    20005         2009-01-08 3
24    10000001    20006         2009-01-01 3
25    10000002    20007         2009-01-01 1
26    10000002    20007         2009-01-02 1
27    10000002    20007         2009-01-03 1
28    10000002    20008         2009-01-01 1
29    10000002    20008         2009-01-02 1
30    10000002    20008         2009-01-03 1
*/

Please join me to congratulate Jim for this wonderful solution!

Congratulations to the Winners of TSQL Challenge 13!

TSQL Challenge #13 was really interesting and several participants mentioned that they really enjoyed solving the problem. We received a large number of solutions, almost all of them were correct and well written. It was very interesting to see how many different ways exist to solve a given problem using TSQL. The evaluation team spent a lot of time testing each solution and finally came up with the three winning solutions.

The Challenge

The challenge is to break a batch of invoices into groups that are not more than 10 rows, without splitting a single invoice into two groups. See the challenge description for the details of this challenge.

And the Winners are …

Here are the winners of TSQL Challenge #13.

• Jim Carter
• Isham Hamin
• Neeraj Mathur

In addition to the regular winning solutions, we would like to present a "featured solution" by Brad Schulz from TSQL Challenge Team. Brad presents a very interesting solution which performs as good as the other solutions, but handles a number of exceptional cases which are not included in the sample data provided with the challenge.

Please join the TSQL Challenge Team to congratulate the winners. We will publish the winning solutions in the next few days.

Did You Try To Solve TSQL Challenge #17 ?

Please note that TSQL Challenge #17 will close on 30th November 2009. This challenge aims to test your SET based query writing skills by presenting a problem where you need to write a query that generates the HTML code for turning some of the keywords in a column into hyper links. Read More »

Did you Receive TSQL Challenges News Letter?

We are coming up with a bi-weekly news letter to keep you updated about what is happening with TSQL Challenges and share with you some of the interesting information we collected during the week (such as interesting SQL Server blogs, white papers etc). The first newsletter went out yesterday (23 Nov 2009) and we had a few ‘first time glitches’, as all of you would expect. An online version of the news letter can be found here. If you are a member of beyondrelational.com, you will get a copy of the news letter by default. If you are not, we would encourage you to Sign up here to get a copy of the bi-weekly news letter.

Did You Take A Look At TSQL Challenge Badges?

Thank you for supporting TSQL Challenges. We have created a number of badges for those of you who had been supporting us continuously. Feel free to use them on your blogs, websites, email signatures etc. Read More »

 

Introducing TSQL Challenge Badges

We had been working on creating a few badges for people who support TSQL Challenges in different ways. This is a recognition of your support for TSQL Challenges through your blogs, web sites etc. We encourage you put these badges on your blog, website...

TSQL Challenge 17 - Creating cross rows references with inline hyperlinks

This is a very interesting challenge which we saw in one of the online forums. The task involves generating HTML tags (hyper links) around certain keywords in the value of a column.

Sample Data

id   keyword              data
---- -------------------- -----------------------------------
1    Pet Store            Microsoft SQL Server
2    SQL Server Database  Dinner at a New York Restaurant
3    Restaurant           Welcome to TSQL Challenges 17
4    New York             Bob is a Database Expert
5    TSQL Challenges      Is Microsoft Listening?
6    Microsoft            New Challenges are coming up

Expected Output

id   keyword              data
---- -------------------- -----------------------------------------
1    Pet Store            <a href="tsql.com?id=6">Microsoft</a> SQL 
						  Server
2    SQL Server Database  Dinner at a <a href="tsql.com?id=4">New 
						  York</a><a href="tsql.com?id=3">Restaurant
						  </a>
3    Restaurant           Welcome to <a href="tsql.com?id=5">TSQL 
						  Challenges</a> 17
4    New York             Bob is a Database Expert
5    TSQL Challenges      Is <a href="tsql.com?id=6">Microsoft</a> 
						  Listening?
6    Microsoft            New Challenges are coming up

Sample Data

Use the following script to generate the sample data needed for this challenge

DECLARE @t TABLE(
	id TINYINT, 
	keyword VARCHAR(20), 
	data VARCHAR(35)
)

INSERT INTO @t(id, keyword, data)
SELECT 1, 'Pet Store', 'Microsoft SQL Server' 
UNION ALL
SELECT 2, 'SQL Server Database', 'Dinner at a New York Restaurant'
UNION ALL
SELECT 3, 'Restaurant', 'Welcome to TSQL Challenges 17'
UNION ALL
SELECT 4, 'New York', 'Bob is a Database Expert'
UNION ALL
SELECT 5, 'TSQL Challenges', 'Is Microsoft Listening?'
UNION ALL
SELECT 6, 'Microsoft', 'New Challenges are coming up'

SELECT * FROM @t

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Last date to submit your entries: 30-Nov-2009 Midnight GMT
4. Use this forum for any questions related to TSQL Challenge #17

Syntax Bookmark of the Day! – Database Mirroring
	Quick reference for database mirroring related TSQL Commands http://syntaxhelp.com/sqlserver/mirroring

About the Authors.

TSQL Challenge 12 - Solution by Matthieu Hodin

I am very excited to write the post for the third winner of TSQL Challenge 12. What makes TSQL Challenges so special is the opportunity to see the different logics and approaches of solving a given problem, in a much more efficient manner than one could think of. No matter how efficient your code is, there is some one else, somewhere in the world, who can almost always write a better query. This is a great learning opportunity that TSQL Challenges brings to the table for all of us to learn and enhance our query writing skills. May be that is what inspires me to stay awake and complete this post at 2 am on a sunday morning!

Solution by Matthieu Hodin

Structurally, the solution submitted by Matthieu Hodin is very close to the solution of Syed. The logic used to generate the next row in the recursive CTE is identical, but the expression used is completely different (and you will find it very interesting). Further, they use two different (but very interesting) logics to terminate the recursive part of the CTE.

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 Matthieu.

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 ScoresGlissant(YearMonth, score)AS 
(
	SELECT yearMonth, score FROM @scores
	UNION ALL
	SELECT 
		YearMonth + 
			CASE 
				WHEN RIGHT(YearMonth,2)=12 THEN 100-11 
				ELSE 1 
			END
		,score
	FROM ScoresGlissant
	WHERE 
		YearMonth < DATEPART(YEAR,GETDATE())* 100 + 
			DATEPART(MONTH,GETDATE())
		AND YearMonth + 
			CASE 
				WHEN RIGHT(YearMonth,2)=12 THEN 100-11 
				ELSE 1 
			END NOT IN (SELECT YearMonth FROM @scores)
)

SELECT YearMonth,Score 
FROM ScoresGlissant
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

We saw that Common Table Expressions (CTEs) are very useful and Recursive CTEs are very powerful and help solving a number of common business problems in a SET based manner. It is not hard to write a recursive CTE. However, when writing a recursive CTE to solve complex problems like the one given in this example, I hink there will be two challenges that might pop up.

• Identify the logic to generate the next row in the recursive result set
• Identify the logic to terminate the recursive process when you are done

There is no single trick to overcome these challenges. What is very important is to identify the right logic for the given problem. Looking at the examples given by experts (like Matthieu and Syed and many others around) we could learn quite a lot. Along with that, doing a bit of hands-on-excercise will certainly make you masters of Recursive CTEs.

I had once written a series of blog posts on recursive queries and I think some of you might find them very interesting.

• Recursive CTE and Ordering of the hierarchical result
• Performing recursive updates using a BOTTOM to TOP recursive CTE
• Performing Recursive Updates using CTE
• Performing a recursive update for more than 32 levels
• Writing a recursive procedure to handle more than 32 levels
• Writing a recursive procedure to update the count of child items under each parent category
• Performing recursive updates in SQL Server
• How to find the dependency chain of a database object?

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

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.

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.

TSQL Challenge #12 – Congratulations to the winners!

We are very glad to announce the winners of TSQL Challenge 12 today. We would like to thank all the participants of TSQL Challenge 12 and would like to congratulate the winners:

1. Greg Holecek
2. Syed Mehroz Alam
3. Matthieu Hodin

We will be posting the winning solutions very soon. TSQL Challenge 16 closes on monday, 17 November 2009, and we would encourage you to take a look at it, if you have not already done so.

Currently the evaluation team is behind by 4 challenges (Results of challenge 12 is announced only when challenge 16 is about to close), but we are working hard to catch up with the publication schedule and reduce the evaluation time to 7 days from the closing date of a challenge.

TSQL Challenge 16 - Find intersections in date ranges and concatenate aggregated labels

Challenge Context

This challenge is derived from a real scenario discussed in a forum. It mainly deals about finding intersections in date ranges.

The original source of data will be a table of persons with their member ship:

                 Description  StartDate   EndDate 	
---------------------------------------------------			
18  Smith  John  Poker Club  01/01/2009  NULL
18  Smith  John  Library     05/01/2009  18/01/2009
18  Smith  John  Gym         10/01/2009  28/01/2009
26  Adams  Jane  Pilates     03/01/2009  16/02/2009

The challenge will be to find each intersection of membership date ranges for each person and produce for this each unique range:

• a new line with the StartDate and EndDate of the range
• Concatenate in the description the descriptions of the memberships for this range with:
  • “/” as separator
  • order by membership StartDate

This is the table of results expected:

18  Smith  John  Poker Club                  01/01/2009  04/01/2009
18  Smith  John  Poker Club / Library        05/01/2009  09/01/2009
18  Smith  John  Poker Club / Library / Gym  10/01/2009  18/01/2009
18  Smith  John  Poker Club / Gym            19/01/2009  28/01/2009
18  Smith  John  Poker Club                  29/01/2009  NULL
26  Adams  Jane  Pilates                     03/01/2009  16/02/2009

Note: The headers were removed here for presentation reasons.

Sample Data

Use the following script to generate the sample data used for this challenge:

SET DATEFORMAT dmy
DECLARE @Membership TABLE ( 
	PersonID	int, 
	Surname		nvarchar(16), 
	FirstName	nvarchar(16), 
	Description nvarchar(16), 
	StartDate	datetime, 
	EndDate		datetime)
	
INSERT INTO @Membership VALUES (18, 'Smith', 'John',
 'Poker Club', '01/01/2009', NULL)
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Library', '05/01/2009', '18/01/2009')
INSERT INTO @Membership VALUES (18, 'Smith', 'John', 
'Gym', '10/01/2009', '28/01/2009')
INSERT INTO @Membership VALUES (26, 'Adams', 'Jane', 
'Pilates', '03/01/2009', '16/02/2009')

Notes

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Last date to submit your entries: Monday 11/17/2009
4. Use this forum for any questions related to TSQL Challenge #16

 

About the Authors.