Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

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!

PLSQL Challenge 3 - Fight Your Fear for Date Calculations

PLSQL Challenge 3 - Fight Your Fear for Date Calculations

Nov 5 2011 3:32AM by Niladri Biswas   

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

  1. If the Date does not exist show it as 'Invalid Date'.
  2. The output should be sorted on the Date result.
  3. 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

    Niladri Biswas
    7 · 21% · 6710
    PLSQL Challenge 3 - Fight Your Fear for Date Calculations , 5.0 out of 5 based on 1 ratings
        Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]