Week Number with custom dates

  • ldanks

    SSChasing Mays

    Points: 660

    Hi All

    I'm trying to pull out a week number but instead of using the default 01-Jan, I want to use a custom year and start date (in my case the year runs from 01-Aug to 31-Jul).  Therefore 01-Aug becomes week number 1.
    Anyone have a clue on how to do that?

  • @Taps

    SSC Eights!

    Points: 870

    There might be more elegant way of doing  but one thing that comes to mind is that you  can get a week number the usual way for a week before 01- Aug (select datepart(week, date)) - - then you can subtract that number from the week numbers after 1st august
    The constant to be subtracted will also depend on which day you want to be 1st day of week - for e.g.  1st Aug 2018  is Wednesday - so do you want to start your week from Wednesday ...


  • ZZartin

    SSC-Dedicated

    Points: 30420

    One option might be to normalize all your dates back to starting on 1/1 then just use DATEDIFF week, there's some trickier here since you'd have to account for leap years so you'd probably want to normalize forward.

    You could also look at the number of days and / by 7.

  • ldanks

    SSChasing Mays

    Points: 660

    Aye, I started with getting the datepart week for 01-Aug and subtracting or adding as required but the leap years got me, and of course the standard week number also ticks forward based on the datefirst setting (1 in my case).  I'm happy for it to follow the same guide as standard week number, therefore 01-Aug is always 1, but the following monday becomes 2.

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    If you work out what the last Monday was, and what the first Monday in August was, you can do a datediff in weeks to get the week number  There's some useful tips on date arithmetic here.

    John

  • ScottPletcher

    SSC Guru

    Points: 98548


    ;WITH
    cteCalcBaseDates AS (
      SELECT DATEADD(MONTH, 7 - CASE WHEN MONTH(GETDATE()) < 8 THEN 12 ELSE 0 END,
      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS Aug01,
      DATEADD(DAY, -1, DATEADD(MONTH, 19 - CASE WHEN MONTH(GETDATE()) < 8 THEN 12 ELSE 0 END,
      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) AS Jul31
    ),
    cteCalcBaseMonday AS (
      SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, Aug01) % 7, Aug01) AS FirstMonday
      FROM cteCalcBaseDates
    ),
    cteTally10 AS (
      SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cteTally100 AS (
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS week#
      FROM cteTally10 c1 CROSS JOIN cteTally10 c2
    )
    SELECT DATEADD(DAY, 7 * (week# - 1), FirstMonday) AS MondayDate, week#
    FROM cteCalcBaseDates
    CROSS JOIN cteCalcBaseMonday
    INNER JOIN cteTally100 t ON t.week# BETWEEN 1 AND 53
    WHERE DATEADD(DAY, 7 * (week# - 1), FirstMonday) <= Jul31
    ORDER BY t.week#

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Chris Harshman

    SSC-Forever

    Points: 42157

    ldanks - Friday, March 29, 2019 8:31 AM

    Hi All

    I'm trying to pull out a week number but instead of using the default 01-Jan, I want to use a custom year and start date (in my case the year runs from 01-Aug to 31-Jul).  Therefore 01-Aug becomes week number 1.
    Anyone have a clue on how to do that?

    Will the custom year always start on the same specified date?  If so it may make sense to have some sort of calendar table or date dimension.  If the custom year is dynamic depending on run time parameters of a report, then you'd have to calculate it on the fly, maybe with a ROW_NUIMBER() function.

  • The Dixie Flatline

    SSC Guru

    Points: 53253


    declare @start date = '2018/08/01'

    ;with tally (N) as (select top 366 row_number() over(order by (select null)) from sys.columns c1 cross join sys.columns c2)
    select Dateadd(day,N-1,@start) as _Date, (n-1)/7+1 as Weekno
    from tally
    where Dateadd(day,N-1,@start) < dateadd(year,1,@Start)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • ldanks

    SSChasing Mays

    Points: 660

    Thank you all for the ideas and the work! There is brilliance here methinks LOL.

    I can just about work out how Dixie's Tally works, and Scott's just breaks my brain trying to break it down so see how it does the magic, but it does give the week numbers I need! And you're right Chris, an additional column on a calendar table is route I think - which would mean using Scott's logic to a table more like Dixie's!

  • ldanks

    SSChasing Mays

    Points: 660

    John Mitchell-245523 wrote:

    If you work out what the last Monday was, and what the first Monday in August was, you can do a datediff in weeks to get the week number There’s some useful tips on date arithmetic here.

    John

    That's an excellent link John - brain busting for me, but definitely going to be useful, thanks.

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

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