今年的WTD与去年的WTD相比-解网

问：

我想确定当年WTD的销售数据的增长，去年同一周的销售数据也是WTD。

因此，如果本周是周一、周二、周三，我试图比较去年同一周的周一、周二、周三，以确定销售增长。从某种意义上说，这需要是动态的，因为它将在每日报告上运行，通过SSRS在前一天结束WTD，SSRS将通过电子邮件发送给各个用户。

我做了大量的在线搜索，并尝试了几次迭代，但都有不良影响。

最新的尝试是

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

这将返回整个星期

Server T-SQL 比较

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

您可能还需要检查我是如何定义上一年的日期的。在最坏的情况下，您可能会遇到一个偏离一周的错误，但考虑到日历的结构，我认为这是无法避免的。

今年的WTD与去年的WTD相比

WTD this year compared to WTD last year

评论

评论