Get Summary Data for Last 52 weeks by Week

  • I have a table which stores shipment data by date. We have a need to get last 52 weeks Shipments by week.

    If today is 11/29/2021, My first week should be between Nov 23-Nov 29 Week 1 , 2020 and so on . Week always starts on Monday and ends on Sunday.

    Table: Shipments , Table has column - 1. ShipDate 2, ShipAmount.

     

    • This topic was modified 1 month, 2 weeks ago by  skb 44459.
    • This topic was modified 1 month, 2 weeks ago by  skb 44459.
  • skb 44459 wrote:

    I have a table which stores shipment data by date. We have a need to get last 52 weeks Shipments by week.

    If today is 11/29/2021, My first week should be between Nov 23-Nov 29 Week 1 , 2020 and so on . Week always starts on Monday and ends on Sunday.

    Table: Shipments , Table has column - 1. ShipDate 2, ShipAmount.

    Excellent. Comprehensive DDL, sample data and desired results, well done.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Actually you're pulling more than 52 weeks' worth that way, but here goes.  Note that since you provided no sample data to test it with, you'll need to do the testing.

    ;WITH CteCalcStartDay AS (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, base_date_1_year_ago) % 7, base_date_1_year_ago) AS start_day
    FROM (
    SELECT DATEADD(YEAR, -1, CAST(GETDATE() AS date)) AS base_date_1_year_ago
    ) AS query1
    )
    SELECT
    DATEADD(DAY, -DATEDIFF(DAY, 0, S.ShipDate) % 7, S.ShipDate) AS ShipWeek,
    SUM(S.ShipAmount) AS ShipAmount
    FROM dbo.Shipments S
    CROSS JOIN CteCalcStartDay C
    WHERE S.ShipDate >= start_day
    GROUP BY S.ShipWeek
    ORDER BY S.ShipWeek

    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."

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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