Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements