tsql - SQL DateDiff 没有周末和公共(public)假期

Question

我正在寻找如何在没有周末和公共(public)假期的两个日期之间选择天数的解决方案。

到目前为止，我有这个:

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

我想要实现的是获得日期范围内的工作日数。问题是最后一步，我试图从这个范围中减去所有公共(public)假期。

任何人都可以帮助完成这最后一步吗？看来，我做错了什么，但我不知道是什么。

先感谢您

Answer 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)