TSQL Challenges

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

Order Table

Expected Output

Scripts

Use the following script to generate the sample data

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