Number weeks from April to September for every year

Question

Post reply

Number weeks from April to September for every year

deepkaur@outlook.com

SSC Enthusiast

Points: 112
More actions
September 18, 2020 at 8:46 pm

#3789571

I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

pietlinden

SSC Guru

Points: 63503

September 19, 2020 at 1:19 am

#3789586

Use a Calendar table?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

What exactly are you hoping for? Some code to generate a calendar table, or some code which takes a date as its input and returns week number? Something else?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

how about:

Declare @StartYear datetime = '1900-04-01' ;
Declare @StartYearNov datetime = '1900-10-01' ;
SELECT convert(date, dateadd(yy,n,@StartYear)) Dt
     , DATEPART(wk, dateadd(yy,n,@StartYear) ) AS Aprilwk
     , DATEPART(wk, dateadd(yy,n,@StartYearNov) ) AS Octoberwk
     , DATEPART(wk, dateadd(yy,n,@StartYearNov) ) - DATEPART(wk, '2020-04-01') AS nWeeks 
from master.dbo.fn_DBA_Tally ( 1 , 250,1 ) 
order by dt

It's a start, you'll get it.

Jeff published a wonderfull article called: Create a Tally Function

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Johan Bijnens wrote:

Jeff published a wonderfull article called: Create a Tally Function

Thank you for the kudo. If you've not subscribed to the article discussion, you may want to. I've found that (especially in SS 2017) that the optimizer occasionally loses it's mind and creates an execution plan that uses the row limit as an "after filter" instead of a "row goal". As a result, it sometimes has the same problem that Itzik Be-Gan had on his first rendition of his GetNums. I have his fix in the code that you've cited but even it doesn't work on occasion (normally on larger numbers of rows above the 1 million mark). I have a new fix that absolutely prevents the problem because the 4th root +1 of the row goal is applied to the original Table Valued Constructor itself.

I just haven't republished it yet because I'm beating it with every stick I can find to make sure of the fix.

I'll add a note to the discussion when it's republished to alert the good folks that subscribed to the discussion either by comment or click.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

deepkaur@outlook.com wrote:

I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL.
Thanks in advance.

Just to be sure, what do you want to do if April 1st occurs on a Saturday (end of the week) and what are you defining as the "end of September"? The full week even if the last day of September occurs on a Sunday?

And how many years do you need all at once?

And you really do need to answer people's questions when you post. Just a tip...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DesNorton SSC-Insane Points: 24386 More actions · Answer 6

If I understand your requirements correctly, something like this should work

First create some data

DECLARE @StartDate date = '2020-04-01'
      , @EndDate   date = '2020-10-01';

DECLARE @DateList table ( THEDATE date PRIMARY KEY CLUSTERED );

WITH   T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
   , NUMS(N) AS (SELECT TOP(DATEDIFF(dd, @StartDate, @EndDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
INSERT INTO @DateList ( THEDATE )
SELECT  TheDate = DATEADD( dd, NM.N - 1, @StartDate )
FROM    NUMS AS NM
OPTION ( RECOMPILE );

Then calculate the week numbers

SELECT dl.THEDATE
     , [WeekDay] = DATENAME(WEEKDAY, dl.THEDATE)
     , [WeekNum] = DENSE_RANK() OVER (ORDER BY DATEADD(dd, DATEDIFF(dd, 6, dl.THEDATE) /7 *7, 6) )
FROM @DateList AS dl
ORDER BY dl.THEDATE

How to post data/code on a forum to get the best help.