Tags

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


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.

PrevSaturday Saturday
NULL 07-01-2012
NULL 14-01-2012
NULL 21-01-2012
NULL 28-01-2012
07-01-2012 04-02-2012
14-01-2012 11-02-2012
21-01-2012 18-02-2012
28-01-2012 25-02-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

PrevSaturday Saturday
NULL 07-01-2012
NULL 14-01-2012
NULL 21-01-2012
NULL 28-01-2012
07-01-2012 04-02-2012
14-01-2012 11-02-2012
21-01-2012 18-02-2012
28-01-2012 25-02-2012
04-02-2012 03-03-2012
11-02-2012 10-03-2012
18-02-2012 17-03-2012
25-02-2012 24-03-2012
NULL 31-03-2012
03-03-2012 07-04-2012
10-03-2012 14-04-2012
17-03-2012 21-04-2012
24-03-2012 28-04-2012
07-04-2012 05-05-2012
14-04-2012 12-05-2012
21-04-2012 19-05-2012
28-04-2012 26-05-2012
05-05-2012 02-06-2012
12-05-2012 09-06-2012
19-05-2012 16-06-2012
26-05-2012 23-06-2012
NULL 30-06-2012
02-06-2012 07-07-2012
09-06-2012 14-07-2012
16-06-2012 21-07-2012
23-06-2012 28-07-2012
07-07-2012 04-08-2012
14-07-2012 11-08-2012
21-07-2012 18-08-2012
28-07-2012 25-08-2012
04-08-2012 01-09-2012
11-08-2012 08-09-2012
18-08-2012 15-09-2012
25-08-2012 22-09-2012
NULL 29-09-2012
01-09-2012 06-10-2012
08-09-2012 13-10-2012
15-09-2012 20-10-2012
22-09-2012 27-10-2012
06-10-2012 03-11-2012
13-10-2012 10-11-2012
20-10-2012 17-11-2012
27-10-2012 24-11-2012
03-11-2012 01-12-2012
10-11-2012 08-12-2012
17-11-2012 15-12-2012
24-11-2012 22-12-2012
NULL 29-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)
)
,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

Advertisements