SQL Puzzle: SQL Advance Query - Find the date of all third Sunday for Year 2017

SQL Puzzle: SQL Advance Query – Find the date of all third Sunday for Year 2017

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to find the date of Third Sunday of all the months.

Input Year:

2017

Expected Output:

Sundays

----------

2017-01-15

2017-02-19

2017-03-19

2017-04-16

2017-05-21

2017-06-18

2017-07-16

2017-08-20

2017-09-17

2017-10-15

2017-11-19

2017-12-17

Solution:

;WITH CTE AS

(

SELECT

DATEFROMPARTS(2017,1,1) FirstDay

,DATENAME(DW, DATEFROMPARTS(2017,1,1)) Day_Name

UNION ALL

SELECT

DATEADD(d,1,FirstDay) FirstDay

,DATENAME(DW, DATEADD(d,1,FirstDay)) Day_Name

FROM CTE

WHERE FirstDay < DATEFROMPARTS(2017,12,31)

)

SELECT FirstDay as Sundays

FROM

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY Months ORDER BY Months) rnk

FROM

(

SELECT *, MONTH(FirstDay) Months FROM CTE WHERE Day_Name = 'Sunday'

) t

)t WHERE rnk = 3

OPTION (MAXRECURSION 0)

Please try the different solution for this puzzle and share it via comment...

Oct 23, 2017Anvesh Patel

Comments: 2

Dinesh.IS

October 24, 2017 at 5:34 am

Declare @StartDate DateTime
Declare @EndDate DateTime;

Set @StartDate=’2017/01/01′
Set @EndDate=’2017/12/31′
;
WITH CTE
As
(Select @StartDate As StartDate,DateName(DW,@StartDate) As Day_Name
Union All
Select DateAdd(Day,1,StartDate),DateName(DW,DateAdd(Day,1,StartDate)) As Day_Name
From CTE Where StartDate<@EndDate)
Select StartDate From (Select Row_Number()Over(Partition By Month(StartDate) Order By StartDate) As RSLNo,* From CTE Where Day_Name='SunDay') A
Where RSLNo=3
OPTION(MAXRECURSION 0)

Reply Cancel

Nitin patel

August 25, 2018 at 11:54 am

declare @startdate datetime =’01-01-2017′
declare @enddate datetime =’12-31-2017′
;with cte as
(
select CAst(dateadd(day,case datename(WEEKDAY,@startdate) when ‘Sunday’ then 14 when ‘Monday’ then 20 when ‘Tuesday’ then 19
when ‘wednesday’ then 18 when ‘thursday’ then 17 when ‘Friday’ then 16 else 15 end,@startdate) as datetime) dt1
union all
select cast(dateadd(day,case datename(WEEKDAY,( dateadd (day,-(DATEPART(dd,(eomonth(DATEADD (MONTH,1,dt1)))))+1,eomonth(DATEADD(MONTH,1,dt1))))) when ‘Sunday’ then 14 when ‘Monday’ then 20 when ‘Tuesday’ then 19
when ‘wednesday’ then 18 when ‘thursday’ then 17 when ‘Friday’ then 16 else 15 end, ( dateadd (day,-(DATEPART(dd,(eomonth(DATEADD (MONTH,1,dt1)))))+1,eomonth(DATEADD(MONTH,1,dt1))))) as datetime)
from cte where dt1< @enddate
)

select * from cte

Reply Cancel

SQL Puzzle: SQL Advance Query – Find the date of all third Sunday for Year 2017

Leave a Reply to Dinesh.IS Cancel reply

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !