除自定义工作日之外的所有记录的总和日期差异

Question

我在谷歌搜索时发现了这个有用的查询：

SELECT
SUM((DATEDIFF(dd, vacFrom, vacTo) + 1)
-(DATEDIFF(wk, vacFrom, vacTo) * 2)
-(CASE WHEN DATENAME(dw, vacFrom)='Friday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, vacTo)='Friday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, vacFrom)='Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, vacTo)='Saturday' THEN 1 ELSE 0 END))
FROM 
MS_users_vacations 
WHERE 
YEAR(vacFrom) = 2018

例如，它的用途是：获取员工一年的总休假天数，如果假期包括任何假期，则不包括周末，因此vacFrom是假期的开始日期，vacTo是结束日期。但是，如果周末是习惯的，如何获得相同的结果？我的意思是，如果我想对所有表记录的日期列(vacFrom、vacTo(之间的天数求和，但不包括工作日，例如：星期一和星期二或我将通过命令参数传递的任何其他日子？

Answer 1

仅使用两个辅助整数变量，DATEDIFF(Day， 0， vacFrom( 和 1+DATEDIFF(Day， 0， vacTo( - 我将命名为 f 和 t - 您可以计算所有这些值。 例如，vacFrom-vacTo 假期的总天数将为 t - f。要减去某些工作日的数量，您可以使用两个整数除法的结果之差来计算它们的数量。例如，t/7 - f/7 是间隔中的星期日数。一般来说，区间中的"x天"数可以计算为差值

(t-x)/7 - (f-x)/7

其中(模 7(：

• x = 0 对于星期日，
• x = 1 对于星期一，
• x = 2 星期二，
• x = 3 为星期三，
• x = 4 星期四，
• x = 5 星期五和
周六 x =
• 6(我更喜欢 x = -1(。

这有效，因为上面 DATEDIFF 表达式中使用的"date 0"是星期一(不依赖于某些区域设置(。 因此，假期间隔中不包括星期日和星期六的天数为

(t - f) - (t/7 - f/7) - ((t+1)/7 - (f+1)/7)

您必须在这些值上构建一个 SUM(就像您在示例中所做的那样(，希望按某个用户 ID 分组(您没有(。

按照您的问题，如果您想要排除星期一和星期四(上面列表中的 1 和 4(，并且想要将这两个(当然不能等于模 7(作为参数传递给查询，则可以使用以下 T-SQL 代码(您可以改用存储过程或表值函数来传递这两个参数(：

DECLARE @par1 int = 1  -- Mondays
, @par2 int = 4; -- Thursdays
SELECT userID
, SUM((aux.t - aux.f)
- ((aux.t-@par1)/7 - (aux.f-@par1)/7)
- ((aux.t-@par2)/7 - (aux.f-@par2)/7)
) AS vacDays
FROM MS_users_vacations
CROSS APPLY (
VALUES (DATEDIFF(Day, 0, vacFrom), 1+DATEDIFF(Day, 0, vacTo))
) aux (f, t)
WHERE YEAR(vacFrom) = 2018
GROUP BY userID;

您也可以只排除一个甚至多个工作日，但如果减去所有 7 个可能的差异，结果将为 0。