sql - 从我的查询中排除周末

Question

我有一个运行良好的查询。该查询基本上检索当前日期之后 6-8 天之间的数据。我想在测量数据的年龄时排除周末。

示例:如果周五注册了一条记录，那么周一早上它会显示为 4 天前的记录，但实际上只有 2 天前的记录，因为周六和周日不应该计算在内。

我尝试了这个，但似乎不起作用:

Select id, name, CreatedDate
from table
where 
CreatedDate <= DATEADD(day, -6, GETDATE()) AND CreatedDate >= DATEADD(day, -8, GETDATE()) -- here I get data between 6-8 days old
 AND ((DATEPART(dw, CreatedDate) + @@DATEFIRST) % 7) NOT IN (0, 1) -- Here im trying to exclude weekends

我做错了什么？

Answer 1

您可以查看日历并尝试此查询

// Logic is very simple. 
// See calendar and try.
// If today is Monday, then Prev8workingdays will include 
// 8 working days + 2 weekends = 12 days. 
// Then result will be dateadd(day,-12, getdate()) = 12 days before today.
// Same logic for other days week
DECLARE @Prev8workingdays date = CASE
    WHEN datepart(dw, getdate()) IN (2,3,4) THEN dateadd(day,-12, getdate()) 
    WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-11, getdate()) 
    ELSE dateadd(day,-10, getdate())
    END
DECLARE @Pre6WorkingDay date = CASE
        WHEN datepart(dw, getdate()) IN (2) THEN dateadd(day,-10, getdate()) 
        WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-9, getdate()) 
        ELSE dateadd(day,-8, getdate())
    END

SELECT sd.* FROM
@SampleDate sd
WHERE sd.CreatedDate >= @Prev8workingdays AND sd.CreatedDate <= @Pre6WorkingDay

引用链接DATEADD