November 29, 2021 at 1:53 pm
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.
November 29, 2021 at 2:08 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 29, 2021 at 3:04 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 29, 2021 at 3:55 pm
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