SQL Server函数排除星期六和星期日

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (305)

我创建了一个“每日销售查询”,它捕获从上一个工作日输入的所有总销售额,该工作日在上午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);
提问于
用户回答回答于

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
;
用户回答回答于

如果您使用案例陈述来确定过去需要多少天,例如

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您的服务器上的工作日是星期一。

扫码关注云+社区

领取腾讯云代金券