Number weeks from April to September for every year

  • deepkaur@outlook.com

    SSC Enthusiast

    Points: 112

    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.

  • pietlinden

    SSC Guru

    Points: 62902

    Use a Calendar table?

  • Phil Parkin

    SSC Guru

    Points: 244780

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Johan Bijnens

    SSC Guru

    Points: 134306

    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


    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[/url] but most of the time this is me

  • Jeff Moden

    SSC Guru

    Points: 997311

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997311

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • DesNorton

    SSC-Insane

    Points: 23322

    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

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

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