首页 文章

没有周末和公共假期的SQL DateDiff

提问于
浏览 次
1

我正在寻找解决方案如何选择两个日期之间的天数，没有周末和公众假期 .

到目前为止我有这个：

SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to
       FROM events AS evnt

一切正常，直到我取消注释部分：

- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

我想要达到的目标是获得日期范围内的工作日数 . 问题出在最后一步，我试图从这个范围中减去所有公共假日 .

最后一步可以帮助任何人吗？看来，我做错了什么，但我无法弄清楚是什么 .

先感谢您

tsql date-range date-difference

4 回答

  • 1

    （我看到这已经回答了，但无论如何我都会抛弃它......）

    将日历表作为一行，而不是单独的public_holidays表，可能不是一个坏主意 . 查看文章SQL Server Calendar Table以获取可演示和可下载的T-SQL代码 . 包括几个用于查询和财政年度的后续文章 .

    回复于
  • 1

    试试这个：

    SELECT evnt.event_id,
   evnt.date_from,
   evnt.date_to,
   DATEDIFF(DD, evnt.date_from, evnt.date_to) 
   - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
   - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
   + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
   - (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
   FROM events AS evnt

    取消注释应该是子查询

    --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

    像这样：

    - (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
    回复于
  • 0

    梶山，

    试试这个：

    SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       -(SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
       FROM events AS evnt

    看起来你错过了 COUNT(*) 之前的 SELECT 声明

    回复于
  • 3

    这是与WITH common_table_expression (CTE)的不同答案

    ;with  t as
(
select COUNT(*) as cnt FROM public_holidays 
WHERE date_from BETWEEN evnt.date_from AND evnt.date_to
)
SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       - (select cnt from T)
       FROM events AS evnt
    回复于

相关问题