函数来排除星期六和星期日
函数来排除星期六和星期日
提问于 2018-12-17 03:40:19
我创建了一个"Daily Sales Query“，它捕获了前一个工作日输入的所有总销售额，该查询在周一至周五上午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);回答 2
Stack Overflow用户
回答已采纳
发布于 2018-12-17 03:46:03
如果使用case语句来确定过去需要去多少天，例如
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是星期一。
Stack Overflow用户
发布于 2018-12-17 07:25:08
您可以使用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
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53805812
复制相关文章
相似问题