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

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