About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Tuesday, July 1, 2014

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

No comments:

Post a Comment