The Challenge
This challenge invites you to play with date calculations. A lot of people are scared of date calculations and this challenge aims to help them to fight their fear!
This challenge is to find the date based on year, month, day of the week and weekday number. Say for example, if the question is to find the date of 2nd Sunday of January 2010, the answer should be '2010-01-10'.
Sample Input Data
Yr Mon Dy Dyno
-------------------------------
2010 Jan Sun 2
2005 Jan Mon 3
1995 Feb Sun 1
2000 Feb Wed 4
1982 Mar Tue 2
2010 Mar Tue 8
Expected Output
Yr Mon Dy Dyno Date
----------------------------------------------
1982 Mar Tue 2 1982-03-09
1995 Feb Sun 1 1995-02-05
2000 Feb Wed 4 2000-02-23
2005 Jan Mon 3 2005-01-17
2010 Jan Sun 2 2010-01-10
2010 Mar Tue 8 Invalid Date
Script
Here is the script to generate the sample input data.
DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)
INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 UNION ALL
SELECT 2005,'Jan','Mon',3 UNION ALL
SELECT 1995,'Feb','Sun',1 UNION ALL
SELECT 2000,'Feb','Wed',4 UNION ALL
SELECT 1982,'Mar','Tue',2 UNION ALL
SELECT 2010,'Mar','Tue',8
SELECT * FROM @tbl
Notes
- The goal is to find the correct date based on the Week Day number regardless of the starting week day of the month. Say, for example January, 2010 starts from Friday and hence Sunday on '2010-01-03' cannot be considered as the 2nd Sunday, although it is in the second week. It will be considered as the 1st Sunday.
- If the Date does not exist show it as 'Invalid Date'.
- The solution should be a single query that starts with "SELECT" or ";WITH"
- The output should be sorted on the Date result.
- Use this forum to discuss your questions related to Challenge 2
- To be able to post questions in the forum, you need to be a member of the group TSQL Beginners Challenges. Click here to subscribe to the group.
- Follow the instructions in the Submission Guidelines to submit your entry.
- The solution should work on SQL Server 2005 and above.
- Last date for submitting your entries is 15 March 2010, Midnight GMT.
T-SQL Challenges have been extremely popular, and today, we are proud that we have the T-SQL Challenge for Beginners. As mentioned earlier in the editorial, the goal of these challenges is very simple.
T-SQL Challenges – It is not necessary that you should know the solution; however, it is expected that you try to solve this challenges and improve your SQL skills. Eventually, you will be improving your understanding of SQL by resolving such challenges.
T-SQL Challenges for Beginners – It is also expected that all the experienced Developers and DBA can easily solve these puzzles. Well, if you cannot, then let us take this as tutorial and improve the skills. If you have at least one year of exposure to T-SQL, you will be able to resolve these simple puzzles easily.
As the goal to T-SQL Challenges for Beginners are little different from T-SQL Challenges, after much thought, the team has come up with very different grading system for the same. We expect that there will be many more entries to Challenges for Beginners, and we want to recognize all those who have presented a correct solution.
Best Solution: Out of all the entries, we will pick the best solution. The solution will be judged mainly on the basis of the performance, query cost or any other optimization parameter.
Unique Solution: In a class, there is always at least one child who has an innovative way to solve problems. It is not necessary that it should be the best solution. Possibly, the solution picked as the unique solution may be the “expensive” route to solve the problem. This unique solution will be evaluated on the basis of the learning value in the solution. If solution has a unique method or interesting usage of common terms and statement, it will qualify in this category. In other words, this solution is like SQL Expert’s Pick
Correct Solution: In this category, we will list the names of all those who have solved this puzzle. We really do not want your learning and experience to go unnoticed. Currently, we are working out on a ranking system, which will help you to keep track of your progress as well.
I hope you all have understood the grading system of T-SQL Challenges for Beginners. Please share your thoughts and suggestions. I sincerely hope that all those who are reading this editorial will participate in T-SQL Challenges.
Regards,
Pinal Dave
http://blog.sqlauthority.com
Well, we have been talking about the beginner’s version of TSQL Challenges for quite some time. We received a number of emails asking for the ETA of TSQL Beginners Challenges. Finally, we are ready to go and I am very glad to make the announcement for the first challenge.
As I always used to say, the goal of TSQL Beginner’s Challenges is to help you learn TSQL and evaluate yourself, so that you know where you are good and where you need improvements.
The goals of TSQL Challenges and TSQL Beginner’s Challenges are completely different and I have tried to differentiate them here.
The Challenge
Let us get started with the challenge. In a nutshell, the challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.
Sample Data
Here is the sample data for this challenge.
EmployeeID EmployeeName Department Salary
----------- --------------- --------------- ---------
1 T Cook Finance 40000.00
2 D Michael Finance 25000.00
3 A Smith Finance 25000.00
4 D Adams Finance 15000.00
5 M Williams IT 80000.00
6 D Jones IT 40000.00
7 J Miller IT 50000.00
8 L Lewis IT 50000.00
9 A Anderson Back-Office 25000.00
10 S Martin Back-Office 15000.00
11 J Garcia Back-Office 15000.00
12 T Clerk Back-Office 10000.00
Expected Output
Here is the output you need to produce from the above sample data.
EmployeeID EmployeeName Department Salary
----------- --------------- --------------- ---------
10 S Martin Back-Office 15000.00
11 J Garcia Back-Office 15000.00
2 D Michael Finance 25000.00
3 A Smith Finance 25000.00
7 J Miller IT 50000.00
8 L Lewis IT 50000.00
Scripts
Run the following scripts to generate the sample data. Use the given sample data to test your solutions.
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)
Notes
- Use this forum to discuss your questions related to Challenge #1
- To be able to post questions in the forum, you need to be a member of the group TSQL Beginners Challenges. Click here to subscribe to the group.
- Follow the instructions in the Submission Guidelines to submit your entry.
- The solution should work on SQL Server 2005 and above.
- Last date for submitting your entries is 31st October 2009, Midnight GMT.
Challenge Moderator
![4TC6XLGO727Z[1] 4TC6XLGO727Z[1]](http://beyondrelational.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/tcb/4TC6XLGO727Z1_5F00_thumb_5F00_1A7B0D3B.jpg)
Tejas Shah is the Moderator for this challenge. Challenge Moderator is the person who will help you out with your questions related to the given challenge. View Profile|Blog.
Hope you will have a great time solving and learning from this challenge. I look forward to hear your suggestions and comments.
Best Regards,
Jacob Sebastian
Submission Guidelines Make sure that your solution uses a SET based approach to solve the problem. Make sure that you create a .sql file with your solution. The file should be named as 'firstname_lastname_tbc_challengenumber.sql". Replace firstname...
I would like to welcome all of you to very first editorial for T-SQL Challenges for Beginners. T-SQL Challenges began with the aim to help community to come out of regular mind set of just reading articles online. There is plenty of reading material available online, but there are very few that can make us use our brain cells.
T-SQL Challenges are very well received in community, and today, we are receiving more than 200 responses for every challenge in a very short time. The real challenge is how to keep everybody involved. T-SQL Challenges is focused and encourage experts to bring out the best from them. T-SQL challenges for Beginners share the same enthusiasm from everyone.
Of course, this challenge can be attempted by everyone and just not beginners. However, just like we say “There is a child in everyone,” – in the exactly same way, I would like to say that “There is a beginner in everyone.” We may be expert in certain areas, but quite often we face a barrier to attempt something new and different. I believe that T-SQL Challenges can initiate the Beginners to break that very barrier. Note that the term beginner is used in a broader sense.
Here, Beginners refer to the new innovators. A problem can be easily resolved by writing simple T-SQL; however, what is most important is that an innovative thought can resolve an age old issue. T-SQL has many dimensions, and each dimension is equally important. These dimensions are creativity, performance, data modeling techniques, readability and many more. A solution, which can be simply solved by cursor, can be re-written using a set-based solution; this will lead to the scoring of some points in the area of performance and best practices.
I want to promise one more thing here that just like T-SQL Challenges, this challenge series for beginners will be focused on building a community, which helps each other. This is an open community, and everyone is welcome to stay as long as they wish. From my experience, I know that this is addictive, but I would let you decide the same.
What can you do to help this community effort?
· Participate in challenge and solve them.
· Read the solutions from others and learn new tricks.
· Spread the word for the challenges.
· Participate by submitting new challenges.
· Last but not least, send your feedback.
One more very interesting point is that if you win the challenge and if you present a very innovative solution, you will receive a special certificate from T-SQL challenge founder Jacob Sebastian and myself. On my side, I promise to feature the same as an article on my blog SQLAuthority.com.
Let me see what you have got? Over to very first challenge now...
Pinal Dave
TSQL Challenges for Beginners is about to roll out and we are very happy to introduce the team of volunteers that run this interesting program. The team consists of a number of SQL Server experts and enthusiasts from all over the world, having experience in diverse domains and who understands the pulses of real life TSQL problems.
One of the key persons in this project is Tejas Shah, a SQL Server expert from Ahmedabad. Tejas is a winner of TSQL Challenges, an active SQL Server blogger and a contributor at MSDN SQL Server forums.
Tejas Shah
Jacob: Tejas, could you give a short description of yourself?
Tejas: I am working with Excellence Infonet as a Team Leader and have 5+ years of experience in Microsoft Technologies like .Net(1.1, 2.0, 3.5), SQL SERVER 2000/2005/2008. I have designed many web based application from small to enterprise scale using .Net, SQL, AJAX, XSLT and also worked on SharePoint development using WSS 3.0 and MOSS 2007, share point designer 2007. Check www.SQLYoga.com frequently for tips, tricks, commentary and ideas on SQL Server.
Jacob: Why did you decide to be part of 'TSQL Challenges for Beginners'
Tejas: TSQL Challenge is a very good community project for an endless journey of SQL Programming.
I like to contribute to the TSQL Challenge to get experience of lots of human beings who are sharing their ideas towards challenges and giving their maximum efforts. This is the reason why I decide to be part of this TSQL Community.
Jacob: Who, according to you will be benefited by 'TSQL Challenges for Beginners'?
Tejas: This will be beneficial to all who want to learn or develop their skills in SQL SERVER. It’s not only for SQL SERVER beginners but experienced developers & readers also can get benefits by trying to solve these challenges because there are lots of small queries / doubts which when we face, then only comes in our thoughts.
Jacob: Your message to the SQL Server developers around the world
Tejas: As per the name suggests it's not only for beginners, other SQL Server developers can also solve these TSQL challenges and recall their knowledge.
Let's share the knowledge by solving the TSQL challenges and be part of "TSQL challenges for Beginners".