这段代码有效,但结果似乎偏离了 1 天
目标:找到给定两个日期的日期差异。
但是,我们将不得不排除
- 周末
- 公众假期
例
2017-11-04 00:00:00和 2017-11-22 10:21:00 之间的区别
预期成果: 12.42 (不包括3个星期六和星期日(
当前结果: 13.42
代码不是我写的,所以我试图理解它。我评论了一些我也不清楚的部分。
/**This part is to find the difference in time for the two dates**/
DECLARE @temp AS DECIMAL(10,2) = CAST(DATEDIFF(hour, CONVERT(time, @StartDate), CONVERT(time, @ENDDate)) as Decimal(10,2))/24.00
Declare @numdays int=0
/**This part is to find the difference in date for the two dates**/
/**Issues likely to come from here**/
IF DATEDIFF(day,@StartDate,@ENDDate)>0
BEGIN
WHILE DATEDIFF(day,@StartDate,@ENDDate)>0
BEGIN
SET @StartDate=DATEADD(d,1,@StartDate)
WHILE exists (SELECT Holiday_Date FROM Holiday where Holiday_Date=CONVERT(date, @StartDate)) or DATENAME(DW,@StartDate)='saturday' or DATENAME(DW,@StartDate)='sunday'
BEGIN
SET @StartDate=DATEADD(d,1,@StartDate)
/**Exclude weekends and PH so we do not add numDays here **/
END
SET @numDays=@numDays+1
END
END
/**Omitted some irrelevant codes here**/
/**Add both differences together to get final result**/
DECLARE @result AS DECIMAL(10,2) = @temp + @numDays
RETURN @result
正在考虑只是纠正逻辑而不是重写整个代码。谢谢!
如前所述,最好的方法是创建一个日历表,然后使用"工作日"列或任何您称之为它的内容来计算差异。
这是伪SQL,在没有工作日历表的情况下,但应该可以让你到达那里:
SELECT YT.{YourColumn},
WD.WorkingDays
FROM YourTable YT
CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
FROM CalendarTable CT
WHERE CT.[DateColumn] >= YT.StartingDateColumn
AND CT.[DateColumn] <= YT.EndingDateColumn
AND CT.WorkingDay = 1) WD
您提到的代码在伪代码中基本上执行以下操作:
while i between @StartDate and @ENDDate
begin
increment i by 1
increment i until it is not a holiday
add one day to result
end
return result
我的观点是这段代码是丑陋和低效的。
- 它使用@StartDate作为"输入参数",即 true 开始日期和循环变量。这是令人困惑的。
- 在非常特殊的情况下,循环和游标的使用是有效的,这当然不是其中之一。
在你的问题中,90%的麻烦是日历表的创建;但你已经有一个了。请勿使用此功能;将代码更改为 Larnu 建议的代码。如果您在这样做时遇到困难,请向我们进一步澄清。
在MySQL 5.1中测试: MySQL 的"工作日"函数为 Mon,5 为 SAT,6 为 SUN,因此你看到下面的 SQL 有一些神奇的不。与 5 和 6。
样本:
select floor((datediff (ed, st)+1) / 7)*2 /*complete week's weekends*/
+ case when floor((datediff (ed, st) +1) % 7) between 0 and 6 /*additional weekends besides complete weeks*/
then case when weekday(ed) >= weekday(st) then least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)) - greatest(5, weekday(st)) + 1,0))
else least(floor((datediff (ed, st) +1) % 7), greatest(least(6, weekday(ed)+7) - greatest(5, weekday(st)) + 1,0)) end
else 0
end as num_of_sat_and_sun
from (select '2019-01-07' as st, '2019-01-12' as ed) x