WTD this year compared to WTD last year-CodePudding

I would like to determine the growth on sales data from current year WTD with sales data from the same week last year also WTD.

So if this week is Mon, Tue, Wed I am trying to compare Mon, Tue, wed from the same week last year to determine the sales growth. This needs to be dynamic in a sense since it will be running on a daily report, ending WTD on the previous day, through SSRS which will be emailed to various users.

I have done copious amounts of online searching and tried several iterations, all with undesirable affects.

The latest attempt being

SELECT 
      [storeid],
      SUM([Sales]) as [2021SalesWTD]
    FROM [dbo].[DailySales2021]
    WHERE CONVERT(date, [date]) >=  DATEADD(DAY, 1-DATEPART(dw, DATEADD(YEAR,-1,GETDATE())),  CONVERT(date, DATEADD(YEAR,-1,GETDATE())))
         AND DATEADD(DAY, 8-DATEPART(dw, DATEADD(YEAR,-1,GETDATE())), CONVERT(date, DATEADD(YEAR,-1,GETDATE())))
    GROUP BY storeid

This returns the entire week

CodePudding user response：

It might help you to try variable declarations.

Here's something I've drafted up:

--Get today's date, find the day of the week (1 indexed from Sunday) and subtract. Add 2 to balance this offset
DECLARE @MondayThisYear AS DATE = CAST(GETDATE()-DATEPART(WEEKDAY, GETDATE()) 2 AS DATE)
--Get the date a year ago. I haven't checked for leap year, add it if you need it
DECLARE @DateLastYear AS DATE = @MondayThisYear-365
--Same idea as MondayThisYear, but using the date from last year
DECLARE @MondayLastYear AS DATE = CAST(@DateLastYear-DATEPART(WEEKDAY, @DateLastYear) 2 AS DATE)
--The number of days that have passed in this working week
DECLARE @WorkingDays AS INT = DATEDIFF(d, @MondayThisYear, GETDATE())

SELECT [insert columns]
FROM [insert table name]
WHERE   ([date column]>@MondayThisYear AND [date column]<GETDATE()) OR
        ([date column]>@MondayLastYear AND [date column]<@MondayLastYear   @WorkingDays)

You might also need to check how I've defined the previous year's dates. In worst case you might get an off-by-one week error, but I don't think that's avoidable given the structure of the calendar.