Query

The Challenge 1

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

view source

print?

1.Yr    Mon    Dy  Dyno

2.-------------------------------

3.2010  Jan  Sun    2

4.2005  Jan  Mon    3

5.1995  Feb  Sun    1

6.2000  Feb  Wed    4

7.1982  Mar  Tue    2

8.2010  Mar  Tue    8

Expected Output

view source

print?

1.Yr     Mon    Dy   Dyno  Date

2.----------------------------------------------

3.1982    Mar    Tue    2   1982-03-09

4.1995    Feb    Sun    1   1995-02-05

5.2000    Feb    Wed    4   2000-02-23

6.2005    Jan    Mon    3   2005-01-17

7.2010    Jan    Sun    2   2010-01-10

8.2010    Mar    Tue    8   Invalid Date

Script

Here is the script to generate the sample input data.

view source

print?

01.DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)

02.INSERT INTO @tbl(Yr, Mon, Dy, Dyno)

03.    SELECT 2010,'Jan','Sun',2 UNION ALL

04.    SELECT 2005,'Jan','Mon',3 UNION ALL

05.    SELECT 1995,'Feb','Sun',1 UNION ALL

06.    SELECT 2000,'Feb','Wed',4 UNION ALL

07.    SELECT 1982,'Mar','Tue',2 UNION ALL

08.    SELECT 2010,'Mar','Tue',8

09.  

10.SELECT * FROM @tbl

Solution: 
select *, case mon when substring(datename(m,dateadd(d,(dyno-1)*7,case Dy when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'01'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'01'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'02'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'02'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'03'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'03'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'04'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'04'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'05'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'05'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'06'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'06'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'07'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'07'end
)),1,3) then convert(varchar,dateadd(day,(dyno-1)*7,case Dy when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'01'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'01'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'02'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'02'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'03'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'03'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'04'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'04'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'05'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'05'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'06'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'06'when substring(datename(weekday,convert(varchar,yr)+'-'+mon+'-'+'07'),1,3) then convert(varchar,yr)+'-'+mon+'-'+'07'end
 



),101 )else 'invalid' end from tbl