SQL Puzzle | Find every Saturday and their corresponding last months Saturday with in a Year

Puzzle Statement

In this puzzle you have to find every Saturday and their corresponding last months Saturday with in a Year. For example in year 2012 consider first two months January and February following table is required. For January obviously we get all null values, For February first Saturday is on 4th Feb so here we have to pull first Saturday from January and so on. If there is no corresponding record then we have show NULL value. See table below for details.

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)
)
,CTE1 AS 
(
	SELECT Start Saturday , Months , ranker 
	FROM 
	(
		SELECT * , ROW_NUMBER() OVER (PARTITION BY Months ORDER BY Months) rnk 
				 , ROW_NUMBER() OVER (PARTITION BY Months ORDER BY (SELECT NULL)) ranker 
		FROM  
		(
			SELECT * , MONTH(Start) Months FROM CTE WHERE DayNamea = 'Saturday'
		) a	
	)a
)
SELECT PrevSaturday , Saturday FROM CTE1 c1
OUTER APPLY
	(
		SELECT Saturday PrevSaturday FROM CTE1 c WHERE c.Months = c1.Months - 1
		AND c.ranker = c1.ranker
	)z
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