查询周交易记录

Question

我目前正在创建一个函数，该函数可以根据交易表的日期返回其周数，但我如何应用日期为的范围：

如果一个月的第一天是星期二到星期六，那么

07/01/2020 - 07/12/2020 = Week 1,
07/13/2020 - 07/19/2020 = Week 2,
07/20/2020 - 07/26/2020 = Week 3,
07/27/2020 - 07/31/2020 = Week 4

当一个月的第一天是星期一或星期日时，回报应该是

06/01/2020 - 06/07/2020 = Week 1,
06/08/2020 - 06/14/2020 = Week 2,
06/15/2020 - 06/21/2020 = Week 3,
06/22/2020 - 06/30/2020 = Week 4

提前谢谢。

Answer 1

根据一个月的开始日，您有不同的分类周的方法。

你可以选择下面的IFELSE逻辑。

DECLARE @transactionDate date = '2020-07-12'
DECLARE @WeekName VARCHAR(30)
IF (datename(weekday,dateadd(day,1,EOMONTH(@transactionDate,-1)))) IN ('Sunday','Monday')
BEGIN
SET @WeekName = (SELECT  CASE WHEN DATEPART(day,@transactionDate) BETWEEN 1 AND 12 THEN 'Week 1'
WHEN DATEPART(day,@transactionDate) BETWEEN 13 AND 19 THEN 'Week 2'
WHEN DATEPART(day,@transactionDate) BETWEEN 20 AND 26 THEN 'Week 3'
WHEN DATEPART(day,@transactionDate) BETWEEN 27 AND 31 THEN 'Week 4' END )
END
ELSE
BEGIN
SET @WeekName = (SELECT  CASE WHEN DATEPART(day,@transactionDate) BETWEEN 1 AND 7 THEN 'Week 1'
WHEN DATEPART(day,@transactionDate) BETWEEN 8 AND 14 THEN 'Week 2'
WHEN DATEPART(day,@transactionDate) BETWEEN 15 AND 21 THEN 'Week 3'
WHEN DATEPART(day,@transactionDate) BETWEEN 22 AND 31 THEN 'Week 4' END 
)
END 
SELECT @WeekName

第2周