Printing next 5 Sunday, Part-2

Read Prev Post : Printing next 5 Sunday, Part-1

Today I got a mail from my community friend Pathik, saying the alternative way to find next 5 sunday.”

I would like to share the same.

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

His approach towards solution was through CTE, let see the solution

declare @dt datetime

declare @howMany int = 5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

    select  DATEADD(d, 7 * number, @dt) as [date],

            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays

    from    cte

OPTION  ( MAXRECURSION 1000 )

date                    Weekdays

----------------------- ------------------------------

2014-07-06 00:00:00.000 Sunday

2014-07-13 00:00:00.000 Sunday

2014-07-20 00:00:00.000 Sunday

2014-07-27 00:00:00.000 Sunday

2014-08-03 00:00:00.000 Sunday

Lets dissect the code and understand each statement.

I am dividing the compete query into 3 part

Part 1 :

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

Here if you see, this piece of code assigns date of upcoming Sunday. We are bifurcating the line

select datediff(wk, 0, getdate())

-- this will give the total number of week from 1900-01-01 to todays date.

Then he added those number of week to the first date of datetime that is it add number of week to 1900-01-01.

So now the date is first day of this week.

select dateadd(wk, datediff(wk, 0, getdate()), 0)

-- this will gives the first day of the current week

Then we are adding 6 to the date we got in previous step, for finding the first Sunday.

Select   DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0))

--it gives the upcoming sunday of current week

Part2:

Now I am taking the CTE written to get the series of incremental numbers starting from 0 to number passed as parameter

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

It’s a Recursive CTE written, where he is trying to generate the series of 0,1,2,3…..@howMany.

Invocation of Anchor: select   0 as number This statement will execute once only and act as a anchor for 1st iteration of execution.

Recursive innovation of routine:

select   number + 1

               from     cte

               where    number < (@howMany - 1)

And each time it executes the result form a anchor for next iteration.

Termination: The iteration will go till the condition matches, i.e  where    number < (@howMany - 1)

This is the termination condition.

Now if you query this CTE it will produce the result

declare @howMany int = 5;

with    cte

          as ( select   0 as number

               union all

               select   number + 1

               from     cte

               where    number < (@howMany - 1)

             )

select * from cte

number

-----------

0

1

2

3

4

Part 3

select  DATEADD(d, 7 * number, @dt) as [date],

            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays

    from    cte     

So it is just just adding 7 days to the first Sunday which we derive in the first step and displaying the name of the day.

Why coders are happy; because they are ignorant creature! J

 

Post Reference: Vikram Aristocratic Elfin Share