TSQL Challenges

TSQL Challenge 23 - Calculate the uptime and downtime of a system by reading the data from an audit log

It is again time to play with some date/time values. This challenge involves reading the audit log entries generated by a monitoring application and create a report that shows the up-time and down-time of a system that the application is monitoring.

Red Gate SQL Backup Pro is sponsoring TSQL Challenge 23. The top winner of TSQL Challenge 23 will win a license of SQL Backup Pro worth $795, and a Red Gate goodie bag including a T-shirt, frisbee and more.

The Context

NP Corporation is using a Legacy Application that their employees use to perform some of the day-to-day operations. The system is quite old, the people that built the system is not around any more. Due to some unknown reasons the system hangs frequently and the only way to bring it up is to reboot the server. Though it is not an acceptable behavior, there is no alternative and the management of NP corporation has decided to migrate to a new system soon. The management wants to keep track of the uptime and downtime of the system and created a monitoring application that populate an audit table as given below.

AuditTime               IsOnline
----------------------- --------
2010-01-01 10:11:23.000 1
2010-01-01 10:14:03.000 1
2010-01-01 10:19:36.000 1
2010-01-01 10:23:59.000 0
2010-01-01 10:27:14.000 0
2010-01-01 10:31:24.000 1
2010-01-01 10:34:15.000 1
2010-01-01 10:36:11.000 0
2010-01-01 10:39:40.000 1
2010-01-01 10:42:23.000 1

The monitoring application checks the status of the application preriodically and logs the status to the audit table. If the server is online, it writes "1" and "0" if the system is not online. "AuditTime" column stores the time at which the monitoring application checked the status of the server. The monitoring application has a custom logic that decides the interval at which the audit takes place.

I know, none of you are happy with the way the system works and the monitoring application behaves. Some of you might even refuse to work in such an environment arguing the whole setup does not make sense. However, for the purpose of this challenge, assume that you have no choice than to live with it!

The Challenge

Your job is to write a query that produces a report showing the uptime and downtime of the system. Here is the output that you need to produce, based on the given input data.

TimeFrom            TimeTo              IsOnline
------------------- ------------------- --------
2010-01-01 10:00:00 2010-01-01 10:21:47 Yes
2010-01-01 10:21:47 2010-01-01 10:29:19 No
2010-01-01 10:29:19 2010-01-01 10:35:13 Yes
2010-01-01 10:35:13 2010-01-01 10:37:55 No
2010-01-01 10:37:55 2010-01-01 11:00:00 Yes

There are a few points to keep in mind when generating the output data.

• The solution should be a single query that starts with a "SELECT", ";WITH" or "WITH"
• The first and last periods start and end on the hour preceding and following. (Note that the "TimeFrom" on the first row is 10 AM and "TimeTo" in the last row is 11 AM. For example, if the first reading is at 08:23:45, the first period in the resultset should start at 8:00:00. If the last reading is at 21:23:45, the last period in the result should end at 22:00:00.
• All rows except for the first and last should finish at the midpoint between the readings. For example the reading at 10:19 found the system to be online and the reading at 10:23 found it to be offline. Since the server went offline some time between the two readings, you should take the midpoint between the readings: 10:21
• The first and last periods should be whatever status the first and last readings indicate (rather than assuming that the period starts up or down).
• The result should use the datetime type, rounded down to the nearest second. Each reading is taken at an exact second - there is no millisecond component.

Sample Data

Here is the script to genreate the source tables and populate the sample data

IF OBJECT_ID('TC23','U') IS NOT NULL BEGIN
	DROP TABLE TC23
END
GO

CREATE TABLE TC23(
	AuditTime DATETIME,
	IsOnline BIT -- 0:Down, 1:Up
)
GO

INSERT INTO TC23(AuditTime, IsOnline)
SELECT '2010-01-01T10:11:23', 1 UNION ALL
SELECT '2010-01-01T10:14:03', 1 UNION ALL
SELECT '2010-01-01T10:19:36', 1 UNION ALL
SELECT '2010-01-01T10:23:59', 0 UNION ALL
SELECT '2010-01-01T10:27:14', 0 UNION ALL
SELECT '2010-01-01T10:31:24', 1 UNION ALL
SELECT '2010-01-01T10:34:15', 1 UNION ALL
SELECT '2010-01-01T10:36:11', 0 UNION ALL
SELECT '2010-01-01T10:39:40', 1 UNION ALL
SELECT '2010-01-01T10:42:23', 1 

SELECT * FROM TC23
/*
AuditTime               IsOnline
----------------------- --------
2010-01-01 10:11:23.000 1
2010-01-01 10:14:03.000 1
2010-01-01 10:19:36.000 1
2010-01-01 10:23:59.000 0
2010-01-01 10:27:14.000 0
2010-01-01 10:31:24.000 1
2010-01-01 10:34:15.000 1
2010-01-01 10:36:11.000 0
2010-01-01 10:39:40.000 1
2010-01-01 10:42:23.000 1
*/

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. Use this forum for any questions related to TSQL Challenge #23
4. Last Date to submit your entries: 22 February 2010 Midnight GMT.

Red Gate SQL Backup Pro is sponsoring TSQL Challenge 23. The top winner of TSQL Challenge 23 will win a license of SQL Backup Pro worth $795, and a Red Gate goodie bag including a T-shirt, frisbee and more.

Note: In case more than one user comes in the first place, we will select the winner through a lucky draw

Special thanks to the Early Review Team, especially Sergejack and dave ballantyne who reviewed the challenge and provided valuable inputs to make the challenge description and rules more comprehensible. Rui, Peter and Rob from the TSQL Challenge Team worked really hard on this challenge to get it to the way you see it now.

TSQL Challenge 16 – Basic Testing Results

We have published the Basic Testing Results of TSQL Challenge 16 at http://databasechallenges.com/sqlserver/tsql/challenge16/basic_testing_results. A few solutions did not pass the Basic testing due to incorrect results. Please note that the number of columns produced by your query should match with the expected results in the challenge description. The order of columns should also match.

We are working on configuring an email notification to the author when a solution fails a certain test. This email will contain detailed information about the reason for failure in most cases. It will take us some more time to have this email notification feature fully functional. In the mean time, you can send an email to tc at beyondrelational dot com see the status of your solution. If you see your solution in the ‘Accepted Solutions’ and if it is NOT in the ‘Basic Testing Results’, it means that the solution has failed. Till we have the email notification feature fully functional, send us email with your questions and we will be happy to send you the details.

We have also started parallel evaluation of TSQL Challenge 17 and will announce the results soon.

TSQL Challenges 16 – Accepted Solutions

This is to give a quick update to those of you who are eagerly waiting for the results of the previous challenges. All the solutions we have received so far for challenge 16 are listed at http://databasechallenges.com/SQLServer/TSQL/Challenge16/Submissions. A few submissions were rejected because they used #temp tables, table variables or local variables.

The list of solutions accepted for further processing are listed at http://databasechallenges.com/SQLServer/TSQL/Challenge16/accepted. If you see your solution in ‘all solutions’ but do not see it in ‘accepted solutions’ it means that your solution is rejected due to one of the reasons given above. If you think that your solution does not fall into the categories I mentioned earlier, and still not listed in ‘accepted solutions’ please let us know and we will double check what is wrong.

While the evaluation of TSQL Challenge 16 is still in progress, we started also the evaluation of Challenge 17. We will post the list of submissions for your review soon. As I had mentioned last week, we are using the new software for the evaluation and the evaluation process for the first couple of solutions will still be slow. However, we will catch up with the challenges soon and our goal is to publish the results of a challenge within 2-3 weeks of publishing it.

TSQL Challenge 22 - Filling balls into boxes based on rules and preferences

Who among us wouldn't want to go back to our childhood and enjoy those lovely playful time once again? Well, here is a challenge that might take you back to your childhood and might keep you busy sorting and filling colorful balls into different boxes. Here is an interesting challenge submitted by our new contributor RamiReddy.

As I have mentioned a few times in the past, we are working very hard on improving 'TSQL Challenges' and a number of people from the SQL Server community have been helping us to come up with some improvements.

We were getting a large number of comments against the 'all-in-one-query' requirement that we put on the challenges. They argue that not all real-world problems are solved with the 'single-query' approach. There are also times when a 'multi-step' query performs better than a 'single-query' approach. They are right.

On the other side, we do get a lot of feedback from people who say that they like the 'one-query' requirement because that is the real 'fun' in these challenges. The 'single-query' requirment makes them think beyond the regular problem solving methods and they find it very interesting. They are right too.

We are still working on setting up a plan that makes both the groups happy. Till then we will allow 'multi-statement' solutions selectively. It means that some challenges will allow multi-statement solutions and some will not. If the challenge allows multi-statement solutions, we will specifically mention it along with the challenge description.

To improve the quality of the challenges and the clarity of the rules of the challenges, we started inviting volunteers to do an 'early challenge review' where they get a chance to review the challenge description and rules and give us some suggestions to improve the quality of the challenges. I am very glad that a few people already came forward to help us and if any of you would like to volunteer, please contact me privately.

Starting with this challenge, we will be providing sample data in physical tables instead of table variables. Your solution should not include the code to populate the sample tables. You can assume that the sample tables with data exist in the current database.

Starting with this challenge, we are also introducing a 'Challenge Moderator'. The moderator of a challenge will be helping you with your questions in the forums and everything else that you would need related to a certain challenge. RamiReddy is the Moderator for this challenge and he will be available to help you with any questions related to this challenge.

We have got some boxes and balls. Our job is to put balls into those boxes. But, wait a second! The balls should be filled into the boxes based on some rules and preferences configured by the user. Here are the rules.

• A box can have only one ball
• A ball can be placed only in one box
• Number of balls and number of boxes will always be the same.
• All boxes should be filled and all balls should be used
• There will be a configuration table where the preferences of the user will be stored. The preference setting should be followed when putting a ball into a box.

In addition to this, there will be a configuration table where the preferences of the user will be stored. The preference setting should be followed when putting a ball into a box.

Here is the sample data for this challenge.

Table: TC22_Boxes

This is the table that stores the boxes. Each row in this table represents a box. Boxes in this table will always be unique.

BoxId       BoxName
----------- --------------------
1           Box 1
2           Box 2
3           Box 3
4           Box 4
5           Box 5
6           Box 6

Table: TC22_Balls

This table stores the balls. Each row in this table represents a ball. Balls in this table will always be unique.

BallId      BallName
----------- --------------------
1           Ball 1
2           Ball 2
3           Ball 3
4           Ball 4
5           Ball 5
6           Ball 6

Table: TC22_Preferences

This table stores the preferences set by the user. There may be multiple preferences configured for each box or ball. For example, the second and third rows indicate that you can put ball #1 or ball #3 into box #2. You can take any one of the preferences but need to make sure that all boxes will be filled and all balls will be used.

PreferenceId BoxId       BallId
------------ ----------- -----------
1            1           1
2            2           1
3            2           3
4            3           2
5            3           3
6            4           1
7            4           2
8            4           3
9            4           4
10           4           5
11           4           6
12           5           4
13           5           5
14           6           5

Here is the expected results based on the above sample data.

Box         Ball      
----------- ----------- 
Box 1       Ball 1           
Box 2       Ball 3           
Box 3       Ball 2           
Box 4       Ball 6           
Box 5       Ball 4           
Box 6       Ball 5           

User the following scripts to generate the sample data for this challenge

-- --------------------------------------------------
-- Create the 'Boxes' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Boxes
END
GO

CREATE TABLE TC22_Boxes
(
    BoxId INT IDENTITY PRIMARY KEY,
    BoxName VARCHAR(20)
)
GO

INSERT INTO TC22_Boxes (BoxName)
SELECT 'Box 1' UNION all
SELECT 'Box 2' UNION all
SELECT 'Box 3' UNION all
SELECT 'Box 4' UNION all
SELECT 'Box 5' UNION all
SELECT 'Box 6' 


-- --------------------------------------------------
-- Create the 'Balls' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Balls','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Balls
END
GO

CREATE TABLE TC22_Balls(
    BallId INT IDENTITY PRIMARY KEY,
    BallName VARCHAR(20)
)
GO

INSERT INTO TC22_Balls (BallName)
SELECT 'Ball 1' UNION all
SELECT 'Ball 2' UNION all
SELECT 'Ball 3' UNION all
SELECT 'Ball 4' UNION all
SELECT 'Ball 5' UNION all
SELECT 'Ball 6' 

-- --------------------------------------------------
-- Create the 'Preferences' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Preferences','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Preferences
END
GO

CREATE TABLE TC22_Preferences(
    PreferenceId INT IDENTITY PRIMARY KEY,
    BoxId INT,
    BallId INT
)
GO

INSERT INTO TC22_Preferences (BoxID, BallID) 
SELECT 1,1 UNION all
SELECT 2,1 UNION all
SELECT 2,3 UNION all
SELECT 3,2 UNION all
SELECT 3,3 UNION all
SELECT 4,1 UNION all
SELECT 4,2 UNION all
SELECT 4,3 UNION all
SELECT 4,4 UNION all
SELECT 4,5 UNION all
SELECT 4,6 UNION all
SELECT 5,4 UNION all
SELECT 5,5 UNION all
SELECT 6,5

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. Use this forum for any questions related to TSQL Challenge #22 

Unlike the previous challenges, this one allows multi-statement solutions. Your solution does not need to be a single-query solution. You can use RBAR, cursors, loops etc in your solution. However, we need to apply some restrictions on your code so that your solution can be evaluated using our automated evaluation script.

Your code should not create objects like views, functions or stored procedures. Your solution should be a single batch. There should not be any 'GO' statements between your solution. We will put the entire content of your solution to the body of a stored procedure and if we do that, your code should not produce an error. For example, you need to make sure that the following works:

CREATE PROCEDURE TC_Eval
AS
BEGIN
SET NOCOUNT ON
-- your code here
END
GO

Your code should not include the code to create the sample data. You can assume that the sample tables already exist in the current database.

We will close this challenge for evaluation on 8 February 2010 Midnight GMT. All the solutions we receive till that date will be processed and the results will be announced in the format given here. However, you can continue to send us your solutions till the day we announce the evaluation results.

1. Are Boxid, BallID and PreferenceId  sequential with no gaps ?
• The column is an identity column and identity columns are not expected to be sequential. There can be gaps/missing-ids.
2. Is there a maximum number of balls and boxes?
• No, there is no maximum number of balls and boxes. The number of balls and boxes will always be the same so that if you fill the boxes correctly, all boxes will be filled and all balls will be used.
3. What sort order is the final result set ?
• Sort the output by Box ID
4. Is there any priority order if there are 2 Balls going into 1 box ?
• No, there is no priority order, because there is only one ‘correct’ box for each ball.
5. Will there be a case in which two balls are preferenced only to one box? For example, can ball3 and ball5 be referenced only to box7 ?
• No, two balls can't be preferenced ONLY to one box. It can happen that ball 3 and 5 are referenced to box 7. But in such a case, either or both of them will also have another preference to another box.
6. Is it guaranteed that the sample data have one and only one valid solution?
• Yes, There will be only on ‘correct’ solution for a given set of sample data. If you assign a ball to an incorrect box, you will end up with another box that does not have any ball to fill in.
7. Can we create indexes on the sample tables?
• No, No modifications should be done to the sample tables.
8. Can I create and use a tally/number table in my queries?
• No, you are not allowed to create and use your own tally tables. However if you need a tally table, you can use the script given here to create a tally table. When you send us your solution, do not include the script to create the tally table. Your query can assume that the tally table already exists on the database where we will run your queries.
9. Can I use a CLR procedure/function
• No, CLR code is not allowed.

• dave ballantyne
• Aurelien Verla
• lmu92
• Sergejack

The Early Review team did a great job by reviewing the challenge description, rules and helped to compile the FAQ list. We have made the required updates to the challenge based on the recommendations of the Early Review Team.

About the Author

Challenge Idea by

TSQL Challenge 16 – Evaluation Started

I kept promising that the speed of the evaluation process will improve soon and some of you may be surprised seeing that the time it takes to publish the results of a challenge is still outside the acceptable time window. The truth is that we are building a work flow system that improves the speed of the evaluation process tremendously and we will be able to publish the results very soon after the deadline of a challenge.

We have thought of delaying the evaluation of challenge 16 so that we could put this challenge through the evaluation work flow system. Though the new system is expected to make the process faster, the first run will take some time because we will have to closely monitor, test and review each step.

We have published the list of submissions we received for challenge 16 at http://databasechallenges.com/SQLServer/TSQL/Challenge16/Submissions. The number of solutions we received for this challenge is surprisingly less. I would invite everyone to take a look at the above location and verify that their submission is listed there. If you don’t see your submission there, please send your solutions again and we will add them quickly.

TSQL Challenge 21 - Reading, recognizing and processing graphical representation of digits.

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters. Here is an example of the output produced by the scanner.

    _  _     _  _  _  _  _
  | _| _||_||_ |_   ||_||_|
  ||_  _|  | _||_|  ||_| _| 
 

Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

• Each digit is represented using 9 cells (3x3)
• Only spaces, underscores and pipe characters are used
• The number of digits in each account number may vary.
• The Scanner is not 100% reliable and it might produce some digits that are invalid

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber
----------- ---------------------------
1            _  _  _  _  _  _  _  _  _ 
            | || || || || |  || ||_ |_|
            |_||_||_||_||_|  ||_| _| _|
                           
2               _  _  _  _  _  _     _ 
            |_||_|| || ||_   |  |  ||_ 
              | _||_||_||_|  |  |  | _|
                           
3            _  _  _     _  _  _  _  _ 
            |_ |_|| || ||_ |_| _|  ||_|
            |_||_||_||_||_||_||_   | _|
                           
4               _  _  _  _  _  _     _ 
            |_||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
5               _  _  _  _  _  _     _ 
            | ||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
6            _     _  _     _  _  _  _ 
            | |  | _| _||_||_ |_   ||_|
            |_|  ||_  _|  | _||_|  ||_|

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value
----------- ---------
1           000007059
2           490067715
3           680X68279
4           490867716
5           X90867716
6           012345678

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

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))

INSERT INTO @t
SELECT	1,--> 000 007 059
' _  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                           
' UNION 
SELECT  2, --> 490 067 715
'    _  _  _  _  _  _     _ 
|_||_|| || ||_   |  |  ||_ 
  | _||_||_||_|  |  |  | _|
                           
' UNION
SELECT	3, --> 680 X68 279
' _  _  _     _  _  _  _  _ 
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                           
' UNION
SELECT	4, --> 490 867 716
'    _  _  _  _  _  _     _ 
|_||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
'  UNION
SELECT	5, --> X90 867 716
'    _  _  _  _  _  _     _ 
| ||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
' 
UNION 
SELECT 6, --> 012 345 678
' _     _  _     _  _  _  _ 
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                           
' 

Notes

1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
4. The number of 3x3 blocks in each record may vary
5. Read the Submission Guidelines and make sure that your solution follows them.
6. The solution should work on SQL Server 2005, 2008 or later versions
7. Use this forum for any questions related to TSQL Challenge #21 

We will close this challenge for evaluation on 25 January 2010 Midnight GMT. All the solutions we receive till that date will be processed and the results will be announced in the format given here. However, you can continue to send us your solutions till the day we announce the evaluation results.

You can submit solutions for challenges even after we publish the results. However, there are a few things you should do before submitting your solutions.

• Do a basic testing of your solution using the sample data we post along with the results of the evaluation. See an example here.
• If your solution passes the basic testing, do a logic testing of your solution. We will publish the data to be used for the logic testing along with the challenge results. You can see an example here.
• If your solution passes the logic testing, do a load testing of your solution using the load testing data that we publish along with the challenge results. You can see an example here.
• After load testing, match your results with the results we have published for that challenge. (see an example here). If the results of your solution comes in the top 10 solutions listed on the results page, go ahead and send us your solution. Make sure that you send us the results of your load testing along with your submission.
• We will re-evalute your solution and if it comes in the top 10, we will list it in the results page and you will be added to the winners page (see an example here) and will get a certain number of SQL Stars based on the grade of your solution.

About the Author

Congratulations to the Winners of TSQL Challenge #15

Results of TSQL Challenge 15 published!

We are glad to announce the results of TSQL Challenge #15. This challenge had comparatively more number of correct solutions and almost all of them were really good in terms of performance and best practices. Here are the top 3 winners of TSQL Challenge #15.

1. Dave Ballantyne
2. Antonio Facturan
3. Fabien Contaminard

The solution by Dave Ballantyne used comparatively very very lesser number of reads and cpu. However, it took more number of seconds than the other two solutions. You can see the performance statistics of all the solutions at http://databasechallenges.com/SQLServer/TSQL/Challenge15/load_testing_results

How to identify the best solution?

Identifying the best solution or ranking them based on the performance is really a challenge. We currently sum up the read, write, cpu and duration ranks of each solution. This method seems to be good, but there are a few arguments here.

If we take the example of this challenge, the solution by Dave Ballantyne came on the top when we calculated the combined rank. Dave’s solution has lesser number of reads and cpu usage. However, the duration is more than the other two solutions. On the other hand, the solution by Antonio has much much more reads and CPU by finishes faster than the other two solutions.

Some people argue that duration of a query is more important than reads/writes/cpu. They say that what people are concerned is only the performance, not reads or writes or even CPU. If the query executes within the expected duration, then it is good, even if the reads or CPU are more than expected.

There are other people who argue that reads/writes/cpu are as important as duration. If a query executes ‘super' fast’ but takes a large amount of CPU, reads and writes, then it will have adverse effect on other queries running on the server. The query being tested may be completing within the required duration limits, but the excessive cpu, reads and writes can slow down many other queries running on the server.

At TSQL Challenges, we currently look at the combined ranks (total of reads, writes, cpu and duration). However, we are eager to know what all of you think about this and are willing to make the required amendments. Please feel free to share your thoughts about this.

 TSQL Challenge 15 – Results

1. TSQL Challenge 15 - All Solutions
2. TSQL Challenge 15 - Accepted Solutions
3. TSQL Challenge 15 - Basic Testing
4. TSQL Challenge 15 - Basic Testing Results
5. TSQL Challenge 15 - Logic Testing
6. TSQL Challenge 15 - Logic Testing Results
7. TSQL Challenge 15 - Load Testing
8. TSQL Challenge 15 - Load Testing Results
9. TSQL Challenge 15 – Winners

We would like to welcome you to the SQL Server Challenge Idea Contest 2010-Q1. Submit an interesting challenge idea or details of a TSQL problem that you have seen, heard or solved. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More >>

TSQL Challenge 20 - Identify repeating digits in Fibonacci Series

This challenge has absolutely no relevance to a real-life problem, but it is very interesting because it tests your programming skills and abstract thinking.

The Fibonacci series is defined as 1, 1, 2, 3, 5, 8, 13, 21, 34, 55 etc, where each number in the series is the sum of the two numbers before it. For example: 34 = 13+21, 55 = 21+34 etc. Some Fibonacci numbers have repeating digits, like 55 for example, which has a single repeating digit. The Fibonacci number 17711 has 2 (different) repeating digits.

Your job is to look through the first 92 Fibonacci numbers (since the 93rd number is beyond BIGINT range) and produce a result set showing the 5 lowest Fibonacci numbers for each quantity of (different) repeating digits.

Expected Output

NumRepeats  FiboNumber
----------- --------------------
1           55
1           144
1           233
1           377
1           317811
2           17711
2           102334155
2           433494437
2           32951280099
2           53316291173
3           27777890035288
3           117669030460994
3           5527939700884757
3           14472334024676221
3           1779979416004714189
4           1100087778366101931

Rules

1. The quantity of repeating digits has to refer to DIFFERENT repeating digits. For example, if there were a Fibonacci number of 344144, this would just have 1 repeating digit, not 2.
2. The repeating digits must be ADJACENT to each other. For example, if there were a Fibonacci number of 34532, the digit of 3 repeats itself, but the 3’s are not adjacent to each other.
3. The repeating digits consist of 2 or more adjacent digits. For example, if there were a Fibonacci number of 23333333, the digit of 3 is considered a single repeating digit. It doesn't matter how many times it repeats.
4. This should be done by a single query.
5. The output should look like the result given above (sorted by NumRepeats, FiboNumber)

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. Use this forum for any questions related to TSQL Challenge #20 

We would like to welcome you to the SQL Server Challenge Idea Contest 2010-Q1. Submit an interesting challenge idea or details of a TSQL problem that you have seen, heard or solved. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More >>

Syntax Bookmark of the Day! – SQL Server Database Mail Syntax
	Quick and detailed reference for SQL Server Database Mail Feature http://syntaxhelp.com/sqlserver/dbmail

About the Author

TSQL Challenge 14 – Winning Solutions

We are just done with the evaluation of TSQL Challenge 14 and the results are published. We received a large number of solutions demonstrating different ways of solving the given problem.

And the top 3 winners are:

1. Aurelien Verla
2. Mark Cowne
3. Adam Machanic

Please join us to congratulate the winners! Their solutions will certainly help to learn better ways of writing TSQL code and solving real-world problems more efficiently.  You can see a list of all the winners here.

The Number Table (or Tally Table)

As I had mentioned a few times in the past, a Number Table (also called as a Tally Table) is an essential tool for every database developer. It helps to solve a number of common TSQL problems using a SET based approach. All the three winning solutions used a tally table to produce the expected output.

Creating and using a Tally Table

If you don’t have a Tally Table in your database, probably it is time to start thinking about building one. There are many ways to create and populate a Tally Table. Some times, for smaller operations, developers even generate a Tally Table on the fly to get a certain operation done. This article might give you some ideas by showing a few different ways of generating a Tally Table.

If you don’t have a tally table, and wants to write a query that uses a Tally Table, you could either generate a Tally Table on the fly using a CTE or Recursive CTE or use the system table master.dbo.spt_values. You can apply a filter for “type = ‘P’” on master.dbo.spt_values to get a sequence of numbers. You might find two issues when trying to use master.dbo.spt_values. First, there may be cases where you dont’t have access to master.dbo.spt_values and the other is that the number of rows in master.dbo.spt_values is limited.

Both Aurelien and Mark used master.dbo.spt_values to solve TSQL Challenge 14. Adam, on the other hand, decided to build a Tally Table on the fly and used it. You can find Aurelien’s solution here, Mark’s here and Adams solution here.

Tally Table for TSQL Challenges

If you wish to use a Tally Table in your solutions for TSQL Challenges, you can take one of the options given below.

1. Use master.dbo.spt_values, if the number of rows in spt_values is sufficient for solving the given problem. However, you need to make sure that your code will not break when we test it with a much larger set of data during our evaluation process.
2. Create and use a Tally Table on the fly. Note that you are not allowed to create table variables, temporary tables or table valued functions in your solutions.
3. The next and probably the best option is to create a Tally Table using the script given here. If you decide to go with this option, you need to take care of the following:
   1. The table should be created exactly as per the script given here.
   2. The name of the table should be “dbo.tsqlc_Tally”
   3. You should not include the code to build this table as part of your solution. You can assume that this table is present in the target system where we evaluate your solutions.

TSQL Challenge 14 – Evaluation Summary

We received 128 submissions for TSQL Challenge 14. Out of that, 41 solutions were rejected due to one or more of the following reasons.

• Using Temp Tables
• Using Table Variables
• Using Local Variables
• Using WHILE Loops
• Using Cursors
• Using UDFs

Note that the challenges are 'always ON', which means that you can submit a solution at any time. You can submit a new solution even after the results are published. If your solution is disqualified, you can re-submit a new version of the challenge at any time.

87 Solutions went through the Basic Testing using the sample data given along with the challenge and only 62 of them passed the test. Those solutions were put through the Logic Testing and only 49 of them passed the test.

The solutions that passed the Logic Test were send to a Load Test with a large table having 100,000 rows. Here is the script that we used to generate the test data for Load Testing. The Load Testing was done with a timeout value of 5 minutes per solution. Solutions that took more than 5 minutes to execute were rejected. Only 20 solutions passed the Load Test and the performance statistics of those solutions are posted here.

Winning Solutions

Solutions that pass the Load Testing will be assigned a rank based on their performance. Based on the rank of each solution, we will consider the top 10 solutions as winning solutions. You can find the Winning Solutions of TSQL Challenge 14 here.

Resubmitting a new solution

Though the results of this challenge is published, you can still submit a new solution. We will evaluate your submissions periodically and will refresh the final results if one of those solutions are better than the solutions already published.

We would like to welcome you to the SQL Server Challenge Idea Contest 2010-Q1. Submit an interesting challenge idea or details of a TSQL problem that you have seen, heard or solved. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More >>

TSQL Challenge 14 – Results of Basic Testing

As many of you have already mentioned, it is a bit frustrating that it takes us almost 8-10 weeks to publish the results of a challenge. We are working very hard on this and you will see a significant improvement in the speed of the evaluation process from now onwards. We are just done with the basic testing of TSQL Challenge 14 and there are unpleasant results.

Results

We received a total of 128 submissions for this challenge, and unfortunately, 41 of them were disqualified because they did not follow the submission guide lines. The rejections fall into the categories given below.

1. Using temp tables
2. Using table variables
3. Using local variables
4. Using WHILE Loops
5. Using Cursors
6. Using UDFs

We completed the Basic Testing phase on the remaining 87 solutions using the data given here. 25 solutions failed the basic testing due to the following reasons.

1. Incorrect output data
2. Incorrect number of output columns
3. Incorrect order of output columns

Note that the evaluation is done by an automated script which will reject a submission if the columns (order and number of columns) do not follow the output structure given in the challenge description.

Resubmit your solutions

If your solution is disqualified due to one of the reasons given above, you don’t need to worry. You can resubmit your solution and after the evaluation is completed, we will review your submission and if it passes the tests will insert it at the right grade in the final results. TSQL Challenges do not close any more and you can always submit a better solution.

Questions

We are in the process of setting up the new website (http://databasechallenges.com/SQLServer/TSQL) with the right structure and would like to hear your ideas on the following:

1. With TSQL challenge 13, we made all the solutions publicly available (even the solutions that failed the tests - http://databasechallenges.com/SQLServer/TSQL/Challenge13/Submissions) Do you think it is a good idea? In TSQL Challenge 14, we only mentioned the number of submissions we received, we did not publish all the solutions we received. Is that a better option?
2. Do you like to see the list of solutions that passes each evaluation phase? (see an example here: http://databasechallenges.com/SQLServer/TSQL/Challenge13/logic_testing_results) Or is it advisable to publish only the count of solutions that passes the given test?
3. Any other suggestion you have, to make the evaluation process better and the challenges and their solutions re-usable?

Thanks for your continuous interest in TSQL Challenges

Jacob

TSQL Challenge 19 - Working with consolidated calculations on hierarchical structures

Working with hierarchies is almost always lot of fun for most of us. Traversing heirarchies and processing data from child or parent nodes is really interesting and such assignments are ideal opportunities to test your logic and query writing skills.

This challenge involves generating an organizational hierarchy and calculating the total orders created by each employee and his/her subordinates. It involves a number of challenges such as generating a resultset in the correct hierarchical form, calculating the orders created by each employee and his/her subordinates and finally calculating the total orders created by self and all the subordinates.

We have been working on making the evaluation process more efficient and transparent. As a result, we came up with a 4 step evaluation process which we have described in detail in this post. We are also working on speeding up the evaluation process and we hope to catch up with the evaluation in a few weeks.

There are a number of things you could do to help us make the evaluation process faster. The first of that is following all the guidelines and rules given in the challenge. If you follow the guidelines, it will help us to complete the evaluation process faster.

When you submit a solution, do not include the code to generate the sample data. So, ideally your query should start with a SELECT or WITH and nothing else. No SET statements, NO setup or clean-up code. Our evaluation process will inject the required code into your solution while processing your solution. Please note that by following these guidlines, you are helping us to complete the evaluation process sooner.

Please pay attention to the rules given at the end of the challenge and the submission guidelines. We currently accept only SET based soutions with a single TSQL query. CTEs are accepted but temp tables, table variables, WHILE loops or UDFs are currently not accepted. A large number of submissions in the previous challenges were rejected because of these reasons. We are working on the evaluation process to see if we can support NON-SET based solutions in the future and we are quite hopeful that we will do it in the future.

Sample Data for TSQL Challenge 19

Employee Table

EmployeeID  FirstName       LastName        ReportsTo
----------- --------------- --------------- -----------
2           Andrew          Fuller          NULL
1           Nancy           Davolio         2
3           Janet           Leverling       2
4           Margaret        Peacock         2
5           Steven          Buchanan        2
8           Laura           Callahan        2
6           Michael         Suyama          5
7           Robert          King            5
9           Anne            Dodsworth       5

Order Table

OrderID     EmployeeID
----------- -----------
10258       1
10270       1
10275       1
10265       2
10277       2
10251       3
10253       3
10256       3
10250       4
10252       4
10248       5
10254       5
10249       6
10289       7
10303       7
10308       7
10262       8
10268       8
10276       8
10278       8
10255       9
10263       9

Expected Output

Name                      Level by_self by_sub total
------------------------- ----- ------- ------ -----
Fuller, Andrew            0     2       20     22
    Buchanan, Steven      1     2       6      8
        Dodsworth, Anne   2     2       0      2
        King, Robert      2     3       0      3
        Suyama, Michael   2     1       0      1
    Callahan, Laura       1     4       0      4
    Davolio, Nancy        1     3       0      3
    Leverling, Janet      1     3       0      3
    Peacock, Margaret     1     2       0      2

Scripts

Use the following script to generate the sample data

DECLARE @emp TABLE (
	EmployeeID INT,
	FirstName VARCHAR(15),
	LastName VARCHAR(15),
	ReportsTo INT
)

DECLARE @ord TABLE (
	OrderID INT,
	EmployeeID INT
)

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
SELECT 2,'Andrew','Fuller',NULL UNION ALL
SELECT 1,'Nancy','Davolio',2 UNION ALL
SELECT 3,'Janet','Leverling',2 UNION ALL
SELECT 4,'Margaret','Peacock',2 UNION ALL
SELECT 5,'Steven','Buchanan',2 UNION ALL
SELECT 8,'Laura','Callahan',2 UNION ALL
SELECT 6,'Michael','Suyama',5 UNION ALL
SELECT 7,'Robert','King',5 UNION ALL
SELECT 9,'Anne','Dodsworth',5
SELECT * FROM @emp

INSERT INTO @ord (OrderID, EmployeeID) 
SELECT 10258,1 UNION ALL
SELECT 10270,1 UNION ALL
SELECT 10275,1 UNION ALL
SELECT 10265,2 UNION ALL
SELECT 10277,2 UNION ALL
SELECT 10251,3 UNION ALL
SELECT 10253,3 UNION ALL
SELECT 10256,3 UNION ALL
SELECT 10250,4 UNION ALL
SELECT 10252,4 UNION ALL
SELECT 10248,5 UNION ALL
SELECT 10254,5 UNION ALL
SELECT 10249,6 UNION ALL
SELECT 10289,7 UNION ALL
SELECT 10303,7 UNION ALL
SELECT 10308,7 UNION ALL
SELECT 10262,8 UNION ALL
SELECT 10268,8 UNION ALL
SELECT 10276,8 UNION ALL
SELECT 10278,8 UNION ALL
SELECT 10255,9 UNION ALL
SELECT 10263,9 

SELECT * FROM @ord

Rules

1. The output should show the hierarchical relationship between the employees (as shown in the example above)
2. The column "by_self" should show all the orders created by the employee (directly)
3. The column "by_sub" should show the total of orders created by all the employees reporting to him/her
4. The column "total" should show the total of "by_self" and "by_sub"
5. The employees shoud be ordered by lastname, firstname

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. Use this forum for any questions related to TSQL Challenge #19 

We will close this challenge for evaluation on 28 December 2009 Midnight GMT. All the solutions we receive till that date will be processed and the results will be announced in the format given here. However, you can continue to send us your solutions till the day we announce the evaluation results.

You can submit solutions for challenges even after we publish the results. However, there are a few things you should do before submitting your solutions.

• Do a basic testing of your solution using the sample data we post along with the results of the evaluation. See an example here.
• If your solution passes the basic testing, do a logic testing of your solution. We will publish the data to be used for the logic testing along with the challenge results. You can see an example here.
• If your solution passes the logic testing, do a load testing of your solution using the load testing data that we publish along with the challenge results. You can see an example here.
• After load testing, match your results with the results we have published for that challenge. (see an example here). If the results of your solution comes in the top 10 solutions listed on the results page, go ahead and send us your solution. Make sure that you send us the results of your load testing along with your submission.
• We will re-evalute your solution and if it comes in the top 10, we will list it in the results page and you will be added to the winners page (see an example here) and will get a certain number of SQL Stars based on the grade of your solution.

We would like to welcome you to the SQL Server Challenge Idea Contest 2010-Q1. Submit an interesting challenge idea or details of a TSQL problem that you have seen, heard or solved. Authors of winning solutions will get a cash prize of $100 and 10 SQL Stars. Read More >>

Syntax Bookmark of the Day! – SQL Server Backup Syntax
	Quick and detailed reference for SQL Server FULL BACKUP command http://syntaxhelp.com/sqlserver/backup/full

About the Author

SQL Server Challenge Idea Contest 2010-Q1

Welcome to the SQL Server Challenge Idea Contest 2010 Q1. SQL Server Challenge Idea Contest is a quarterly challenge from SQL Server Challenges team to recognize people with great SQL Server experience solving complex business problems using TSQL.

If you have a great SQL Server Challenge idea, send us your challenge idea and we will be very happy to review them.

What are challenge ideas?

Any business problem that requires above average SQL Server skills to solve may be qualified as an ideal candidate for the SQL Server Challenge Idea Contest. It could be one of the following:

1. Any problem that you came across and successfully solved.
2. Any problem that you have come across but was not able to solve.
3. Any difficult problem that you have seen, heard of can think of.

Challenge Guidelines

The challenge idea you submit should follow the guidelines given below.

1. It should be a problem that can be solved using TSQL.
2. It should be a data transformation challenge – taking data from one or more tables and producing the output in a certain format.
3. You should provide a description of the challenge.
4. You should provide detailed list of the rules, logic requirements and additional details such as sort order etc, along with your submission.
5. you should provide the sample data and expected results. The sample data should be simple, short and minimal and should focus on explaining the concept of the challenge
6. You should provide the scripts to create the sample data
7. Though not mandatory, it is recommended that you provide a more detailed data for performing the logic testing of the solutions. This data should contain a more detailed tricky set of data which has all sort of odd and exceptional value combinations.
8. Finally, though it is not mandatory, we will be very happy if you can provide the script for generating the load testing data. The load testing is done with a larger data set, which is usally 1 million rows.

And the Winners Get…..

The authors of winning solutions will receive a few exciting prizes. The challenge idea that comes first will receive a cash prize of $100 by Paypal. In addition, the winner will get 10 SQL Stars and the challenge will be published at www.tsqlchallenges.com.

The SQL Server Challenges team will also select top (up to) 10 challenge ideas from the submissions and the authors of those challenges will get a consolation cash prize of $25 each. They will also earn 5 SQL Stars each for their interesting challenge idea. Selected challenge ideas will be published at www.tsqlchallenges.com.

Submitting the challenge idea

Follow the guidelines given below for submitting your challenge idea.

1. Create a text file that explains your challenge idea. Name the file “firstname_lastname_SSCIC2010Q1.txt”. If your first name is “smith” and last name is “jones”, your file should be named “smith_jones_SSCIC2010Q1.txt”. Include the source data and expected output in the text description.
2. Create a sql file that contains the scripts to create the input data. Use table variables instead of physical/temp tables and provide the insert scripts. Include your version of the solution (if you were able to solve the problem) in the sql file. Name the file “firstname_lastname_SSCIC2010Q1.sql”. If your first name is “smith” and last name is “jones”, your file should be named “smith_jones_SSCIC2010Q1.sql”.
3. Send both the files by email to tc@beyondrelational.com. We will send a confirmation email when we receive the submission. If you do not receive a confirmation message in 24 hours, please resend your submission.
4. Last date for submitting your entries is 31 January 2010 Midnight GMT. The winners will be announced by the second week of February.

Questions

Use this forum to discuss your questions on this contest.

Good Luck

Best of luck all the participants and we look forward to see your submissions soon.

TSQL Challenges – Announcing the new improved evaluation process!

One of the areas we always knew that we need improvement, is the evaluation process. We had been constantly working on improving the evaluation process to improve the accuracy, quality and transparency of the process. While we were still working on getting into a better process, we faced a major glitch with the evaluation of TSQL Challenge 13. This mistake made us focus fully on the evaluation process, under ‘emergency mode’ and set the process right. As a result, I am very happy to announce the new evaluation process which makes the whole process very transparent and provides more accurate results. Many thanks to all the members of TSQL Challenges Team who spent quite a lot of time working on the evaluation process, especially Adam, David, Brad and Rui (and Jacob too??).

A more transparent Evaluation Process

We started doing a multi-step evaluation process starting from TSQL Challenge 13. The evaluation process includes:

1. Pre Process Validation
2. Basic Testing
3. Logic Testing
4. Load Testing

To make the evaluation process more transparent, we will be posting the solutions, data used for the testing, test results etc on a new website (http://sqlserverchallenges.com). Please take a look at the results of TSQL Challenge 13 and we are sure you will like it :-).

Pre Process Validation

The evaluation process starts with ‘Pre Process Validation’ where the solutions will be reviewed and verified whether they follow the submission guide lines. For example, solutions that use cursors or temp tables will be rejected at this stage. Once the invalid submissions are removed, the evaluation process moves ahead with the Accepted Solutions. We will publish the list of accepted solutions after the Pre Process Validation is completed. You can see an example here.

Basic Testing

During this stage, the solutions will be tested against the sample data we publish along with the challenge description. We will only test the accuracy of the solution at this stage. Performance statistics are not collected at this testing phase. Any solution that does not produce the EXACT output described in the challenge description will be rejected. Note that the number of columns and the values of every row should match 100% with the Expected Output listing given in the challenge description. We will publish the solutions that pass the Basic Testing once the testing is done. You can see an example here.

Logic Testing (tricky testing?)

Solutions that pass the Basic Testing will go through a tougher Logic Testing. This testing will be done on a set of ‘tricky’ data where the robustness and the strength of the logic applied in the solution will be tested. After the evaluation, we will publish the tricky data so that you can take a look at the data used for the testing. You can see an example here. Solutions that pass the Logic testing will be published as done with TSQL Challenges 13 here.

Load Testing

Finally, the solutions will go through the Load Testing phase. During Load Testing, we will test the solutions with a very large set of data. We will publish the data for your reference, after the evaluation is over. Note that this is the only testing where we collect the performance statistics of each solution. We will publish the performance details of each solution after the evaluation is over. You can find an example here.

Announcing the Winners

After the Load Testing, we will order the results based on the rank (which includes Reads, Writes, Duration and CPU) and will consider the top 10 solutions as winning solutions. Based on the rank, each winner will get 1 to 10 SQL Stars. We will also create a page where the total SQL Stars each person earned will be published. A higher number of SQL Stars indicates a higher level of query writing skills. Here are the winners of TSQL Challenge 13.

Comments and Questions

We will be very happy to hear your comments, feedback and questions on the evaluation process.

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.