SQL Puzzle | Find every 3rd Tuesday in each month with in a Year

Puzzle Statement

In this puzzle you have to find every 3rd Tuesday in each month with in a Year. For example year 2012. Well this question was asked at Noida based IT company. You cannot use a loop or cursor. Please check input and expected output for details.

Sample Input

Year = 2012

Expected output

 Tuesdays 17-01-2012 21-02-2012 20-03-2012 17-04-2012 15-05-2012 19-06-2012 17-07-2012 21-08-2012 18-09-2012 16-10-2012 20-11-2012 18-12-2012

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

 ```-- ;WITH CTE AS ( SELECT DATEFROMPARTS(2012,1,1) Start , DATENAME(DW, DATEFROMPARTS(2012,1,1)) DayNamea UNION ALL SELECT DATEADD(d,1,Start) Start , DATENAME(DW, DATEADD(d,1,Start)) DayNamea FROM CTE WHERE Start < DATEFROMPARTS(2012,12,31) ) SELECT Start Tuesdays FROM ( SELECT * , ROW_NUMBER() OVER (PARTITION BY Months ORDER BY Months) rnk FROM ( SELECT * , MONTH(Start) Months FROM CTE WHERE DayNamea = 'Tuesday' ) a )a WHERE rnk = 3 OPTION ( MAXRECURSION 0 ) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal

Http://MSBISkills.com