函数排除周六和周日

Question

我创建了一个"每日销售查询"，它捕获上一个工作日(周一至周五上午8点(输入的所有总销售额。

问题是，如果今天是星期一，我该如何获取星期五的记录。这样我就可以排除周末了。

因为如果是星期一，总销售额显示为0，这实际上是有道理的，因为星期天不是工作日。请协助。

查看我当前的代码：

SELECT 
CONVERT(VARCHAR, DATEADD(dd, - 1, GETDATE()), 103) AS Date, 
'Sales Orders' AS Type, 
COUNT(o.SalesOrderID) AS Orders, 
SUM(d.QtyOrdered) AS Chairs, 
ISNULL(ROUND(SUM(d.ExtendedPrice), 2), 0) AS [Total Ex GST] 
FROM 
dbo.SalesOrder o
LEFT OUTER JOIN 
dbo.SalesOrderDetails d ON o.SalesOrderID = d.SalesOrderID 
WHERE 
(o.EntryDate >= CONVERT(CHAR(8), DATEADD(dd, - 1, GETDATE()), 112)) 
AND (o.EntryDate < CONVERT(CHAR(8), GETDATE(), 112)) 
AND (o.CustomerID <> 187);

Answer 1

无论其他服务器设置如何，您都可以使用datediff(dd,0,getdate()) % 7 = 0来确定当前日期是否为星期一(这是因为SQL server中的零日期是1900-01-01，恰好是星期一(。

declare @start date;
declare @finish date;
set @start = dateadd(dd, case when datediff(dd,0,getdate()) % 7 = 0 then -3 else -1 end, getdate());
set @finish = dateadd(dd,1,@start);
select
@start, datename(weekday,@start)
, @finish, datename(weekday,@finish)
, datename(weekday,getdate())
;

所以在你的查询中，我会使用：

declare @start date;
declare @finish date;
set @start = dateadd(dd, case when datediff(dd,0,getdate()) % 7 = 0 then -3 else -1 end, getdate());
set @finish = dateadd(dd,1,@start);
SELECT 
CONVERT(VARCHAR, @start, 103) AS Date, 
'Sales Orders' AS Type, 
COUNT(o.SalesOrderID) AS Orders, 
SUM(d.QtyOrdered) AS Chairs, 
ISNULL(ROUND(SUM(d.ExtendedPrice), 2), 0) AS [Total Ex GST] 
FROM 
dbo.SalesOrder o
LEFT OUTER JOIN 
dbo.SalesOrderDetails d ON o.SalesOrderID = d.SalesOrderID 
WHERE 
o.EntryDate >= @start
AND o.EntryDate < @finish
AND o.CustomerID <> 187
;

Answer 2

如果您使用案例语句来确定过去需要进行的天数，例如

dateadd(dd, case when datepart(weekday,getdate()) = 1 then -3 else -1 end, getdate()) -- StartDate
dateadd(dd, case when datepart(weekday,getdate()) = 1 then -2 else 0 end, getdate()) -- EndDate

所以你的代码看起来像

SELECT 
CONVERT(VARCHAR, dateadd(dd, case when datepart(weekday,getdate()) = 1 then -3 else -1 end, getdate()), 103) AS Date, 
'Sales Orders' AS Type, 
COUNT(o.SalesOrderID) AS Orders, 
SUM(d.QtyOrdered) AS Chairs, 
ISNULL(ROUND(SUM(d.ExtendedPrice), 2), 0) AS [Total Ex GST] 
FROM 
dbo.SalesOrder o
LEFT OUTER JOIN 
dbo.SalesOrderDetails d ON o.SalesOrderID = d.SalesOrderID 
WHERE 
(o.EntryDate >= CONVERT(CHAR(8), dateadd(dd, case when datepart(weekday,getdate()) = 1 then -3 else -1 end, getdate()), 112)) 
AND (o.EntryDate < CONVERT(CHAR(8), dateadd(dd, case when datepart(weekday,getdate()) = 1 then -2 else 0 end, getdate()), 112)) 
AND (o.CustomerID <> 187);

PS：请确认您服务器上的工作日1是星期一。