我想确定增长的销售数据,从本年度的WTD和销售数据,从同一周,去年也WTD。
因此,如果这周是蒙,图,韦德,我试着比较孟,图,从去年的同一周结婚,以确定销售增长。这需要在某种意义上是动态的,因为它将在每日报告上运行,在前一天结束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
这将返回整个星期。
发布于 2022-11-15 23:54:36
它可能会帮助您尝试变量声明。
以下是我起草的一些东西:
--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)
您可能还需要检查我是如何定义前一年的日期的。在最坏的情况下,你可能会出现一周的错误,但考虑到日历的结构,我不认为这是可以避免的。
https://stackoverflow.com/questions/74451733
复制相似问题