Home Forums SQL Server 2017 SQL Server 2017 - Development How to calculate Work Week based on the Next logic RE: How to calculate Work Week based on the Next logic

  • February 17, 2019 at 4:12 pm

    #2022234

    amir.kachlon - Sunday, February 17, 2019 8:12 AM

    Thanks for your insight ! that was fast 🙂

    so would this script be the most efficient

    case
    when DATEPART(WEEK,Cal_Date)=53 and DATENAME(WEEKDAY,DATEADD(yy, DATEDIFF(yy, 0, Cal_Date) + 1, -1))<>'Saturday'
    then 1
    else DATEPART(WEEK,Cal_Date)
    end as Work_Week

    I believe the following will do it for you for every week that the DATETIME datatype can handle except for dates < 07 Jan 1753 or dates > 25 Dec 9999.


     SELECT Work_Week = (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'07 Jan 1753',SomeColumn)/7*7+6,'07 Jan 1753'))+6)/7
       FROM dbo.SomeTable

    --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)