Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Need counts of orders per week grouped by week RE: Need counts of orders per week grouped by week

  • February 2, 2018 at 3:40 pm

    #1978422

    meichmann - Friday, February 2, 2018 9:27 AM

    Jeff Moden - Thursday, February 1, 2018 4:53 PM

    meichmann - Thursday, February 1, 2018 9:14 AM

    ScottPletcher - Thursday, February 1, 2018 7:58 AM


    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date]) AS [Week of],
      COUNT(*) AS Count
    FROM dbo.your_table_name
    GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date])

    This code is exactly what I need!  Thank you so much!

    Fast forward 1 year to 2019 using the same day of the month for January.  Because January 2019 starts on a Tuesday instead of a Monday, what would you want for the answer?

    I'm not concerned what day the month or year starts.  I'm more concerned with the start of the week.  this query works as needed. but thanks !

    I get that... just remember that if you believe that Monday's should be the first day of the week (and, I do), that the first day of the first week in January of 2019 is actually the last day of the year for 2018.  Is that going to be acceptable for your reporting purposes?

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