November 2009 - Posts
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.
- The resulting output is a single 31-character column called Calendar
- The Month should be uppercase and should be rendered in the language that is set at runtime
- The Month and Year are centered
- 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
- The calendars must be sorted in order
- 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
- Read the Submission Guidelines and make sure that your solution follows them.
- The solution should work on SQL Server 2005, 2008 or later versions
- Last date to submit your entries: Dec 14 2009 Midnight GMT
- Use this forum for any questions related to TSQL Challenge #18
| Syntax Bookmark of the Day! – IDENTITY Functions and commands |
 |
|
About the Author
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:
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.
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.
- 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.
- 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.
- 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!
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!
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.
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 »
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...
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
- Read the Submission Guidelines and make sure that your solution follows them.
- The solution should work on SQL Server 2005, 2008 or later versions
- Last date to submit your entries: 30-Nov-2009 Midnight GMT
- Use this forum for any questions related to TSQL Challenge #17
| Syntax Bookmark of the Day! – Database Mirroring |
| |
|
About the Authors.
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.
Have you ever solved a TSQL problem using a recusrive query? We invite you to share your Recursive CTE experience with us.
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.
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.
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:
- Greg Holecek
- Syed Mehroz Alam
- 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.
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
- Read the Submission Guidelines and make sure that your solution follows them.
- The solution should work on SQL Server 2005, 2008 or later versions
- Last date to submit your entries: Monday 11/17/2009
- Use this forum for any questions related to TSQL Challenge #16
About the Authors.