Printing next 5 Sunday, Part-3

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

                         : Printing next 5 Sunday, Part-2

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

This is again another approach to find the next 5 Sunday, here we removed the CTE which we used in the last post, and replace it with Rank function.

declare @dt  datetime

declare @howMany integer =5

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

select top (@howMany)  dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt) as weekdays,

DATENAME(dw, DATEADD(d, 7 * (cast(ROW_NUMBER() over (order by name) as int) - 1), @dt)) as Weekdays

from sys.objects

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

Let’s dissect the code and understand each statement.

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

Lets divide this statement further more

cast(ROW_NUMBER() over (order by name) as int) – 1

This will give list of numbers starts from 0,1,2,3…….

Here below line will multiply 7*(0,1,2,3….) and add the number of days in multiple of 7 to the @dt date

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

So if the @dt is 2014-07-06

In first Iteration

select dateadd(d,7*(0),'2014-07-06')

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

2014-07-06 00:00:00.000

In second Iteration

select dateadd(d,7*(1),'2014-07-06')

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

2014-07-13 00:00:00.000

I am a coder, I have given my life to you, give me smile now! J

 

Post Reference: Vikram Aristocratic Elfin Share

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