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 output should be sorted on the Date result.
- 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.
Sample Script
CREATE TABLE PLC3_TBL(
Yr INT
,Mon VARCHAR2(50)
,Dy VARCHAR2(50)
,Dyno INT);
INSERT INTO PLC3_TBL(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 FROM DUAL UNION ALL
SELECT 2005,'Jan','Mon',3 FROM DUAL UNION ALL
SELECT 1995,'Feb','Sun',1 FROM DUAL UNION ALL
SELECT 2000,'Feb','Wed',4 FROM DUAL UNION ALL
SELECT 1982,'Mar','Tue',2 FROM DUAL UNION ALL
SELECT 2010,'Mar','Tue',8 FROM DUAL;
SELECT * FROM PLC3_TBL;
Restrictions
The solution should be a single query that starts with a "SELECT" or “WITH”
Notes
Read the Submission Guidelines given in the FAQ section to ensure that your submission follows all the required guidelines.
If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the solution will be executed.
The solution should work on Oracle 10g or later versions.
Tags:Oracle,DATE, DATETIME