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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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