This puzzle is part of the TSQL Challenge contest.
Author: Divya Agrawal Shah
Introduction
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 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 Results
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
Rules
- 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.
Sample Script
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
Restrictions
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.
- The solution should work on SQL Server 2005 and above.