考虑到周末和假期的两个日期之间的TSQL功能 [英] TSQL function to find difference between two dates taking weekends and holidays into account
本文介绍了考虑到周末和假期的两个日期之间的TSQL功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Tomalak 对现有的SO问题发表了很好的回应：
它几乎适用于我，但我需要计算两个日期之间的营业时间差异，不包括周末，即使不到一周。我的解决方案增加了一个循环（这可能是一个天真的，开放的建议！），并添加一个假期查找表中的假期检查。
编辑：
我不是在寻找这个传统意义上的回答，只是想回到容易地打开它，以备将来可能遇到的人批评。
ALTER FUNCTION dbo.udfDateDiffBusinessHours（
@ date1 DATETIME，
@ date2 DATETIME
）RETURNS DATETIME AS
BEGIN
DECLARE @sat INT
DECLARE @sun INT
DECLARE @workday_s INT
DECLARE @workday_e INT
DECLARE @ basedate1 DATETIME
DECLARE @ basedate2 DATETIME
DECLARE @ calcdate1 DATETIME
DECLARE @ calcdate2 DATETIME
DECLARE @iteratordate DATETIME
DECLARE @cworkdays INT
DECLARE @coffdays INT
DECLARE @returnval INT
SET @workday_s = 480 - 工作日开始：8小时
SET @workday_e = 1080 - 工作日结束：18小时
- 根据SET DATEFIRST选项计算星期六和星期日
SET @sat = CASE @@ DATEFIRST W HEN 7 THEN 7 ELSE 7 - @@ DATEFIRST END
SET @sun = CASE @@ DATEFIRST WHEN 7 THEN 1 ELSE @sat + 1 END
SET @ calcdate1 = @ date1
SET @ calcdate2 = @ date2
- @ date1：假设第二天如果开始是在工作日结束后
SET @ basedate1 = DATEADD（dd，0，DATEDIFF（dd，0， @ calcdate1））
SET @ calcdate1 = CASE WHEN DATEDIFF（mi，@ basedate1，@ calcdate1）> @workday_e
THEN @ basedate1 + 1
ELSE @ calcdate1
END
- @ date1：如果是星期六或星期天，下周一让
SET @ basedate1 = DATEADD（dd，0，DATEDIFF（dd，0，@ calcdate1））
SET @ calcdate1 = CASE DATEPART（dw，@ basedate1）
WHEN @sat THEN @ basedate1 + 2
WHEN @SEN THEN @ basedate1 + 1
ELSE @ calcdate1
END
- @ date1：假设@workday_s作为最小开始时间
SET @ basedate1 = DATEADD（dd，0，DATEDIFF（dd，0，@ calcdate1））
SET @ calcdate1 = CASE WHEN DATEDIFF（mi，@ basedate1，@ calcdate1） @workday_s
THEN DATEADD（mi，@workday_s，@ basedate1）
ELSE @ calcdate1
END
- @ date2：假设前一天如果结束是在开始之前的工作日
SET @ basedate2 = DATEADD（dd，0，DATEDIFF（dd，0，@ calcdate2））
SET @ calcdate2 = CASE WHEN DATEDIFF（mi，@ basedate2，@ calcdate2） @workday_s
THEN DATEADD（mi，@workday_e，@ basedate2 - 1）
ELSE @ calcdate2
END
- @ date2：如果星期六或星期日，它以前的星期五
SET @ basedate2 = DATEADD（dd，0，DATEDIFF（dd，0，@ calcdate2））
SET @ calcdate2 = CASE DATEPART（dw，@ calcdate2）
WHEN @ THEN @ basedate2 - 0.00001
WHEN @sun THEN @ basedate2 - 1.00001
ELSE @ date2
END
- @ date2：假设@workday_e为最大结束时间
SET @ basedate2 = DATEADD（dd，0，DATEDIFF（dd，0，@ calcdate2））
SET @ calcdate2 = CASE WHEN DATEDIFF（mi，@ basedate2，@ calcdate2）> @workday_e
THEN DATEADD（mi，@workday_e，@ basedate2）
ELSE @ calcdate2
END
- 计算整个工作日（减去星期六，星期日和节假日）
SET @cworkdays = DATEDIFF（dd，@ basedate1，@ basedate2）
SET @iteratordate = @ basedate1
SET @coffdays = 0
WHILE DATEDIFF ，@iteratordate，@ basedate2）> 0
BEGIN
如果DATEPART（dw，@iteratordate）= @sat OR DATEPART（dw，@iteratordate）= @sun或EXISTS（SELECT holidaydate FROM dbo.holidays_lu（NOLOCK）WHERE holidaydate = @iteratordate）
SET @coffdays = @coffdays + 1
SET @iteratordate = DATEADD（dd，1，@iteratordate）
END
SET @cworkdays = @cworkdays - @coffdays
- 以分钟计算有效持续时间
SET @returnval = @cworkdays *（@workday_e - @workday_s）
+ @workday_e - DATEDIFF（mi，@ basedate1，@ calcdate1）
+ DATEDIFF（mi，@ basedate2，@ calcdate2） - @workday_e
- 返回持续时间作为从日期0分钟的偏移量
RETURN DATEADD（mi，@returnval，0）
END
解决方案
通过这个解决方案：
使用工作日历表，您可以根据需要对工作日（包括节假日）进行调整。
计算营业时间将是查询：
- 开始和结束日期之间的工作日，不包括开始和结束日期 *（工作时间/天）
- +开始日的工作时间（如果是工作日）
- +结束日的工作时间（如果是工作日）
也许可以将工作时间编码到工作日历表中，但我还没有尝试过。 >
Tomalak posted an excellent response to an existing SO question:
It almost worked for me but I needed to compute the business hours difference between two dates, excluding weekends, even if less than a week had elapsed. My solution adds a while loop (which is probably a bit naive, open to suggestions!) and adds a check for holidays in a holiday lookup table.
Edit: I'm not looking for this to be answered in the traditional sense, just wanted to get back to it easily and open it up for critiquing for those who may come across it in the future.
ALTER FUNCTION dbo.udfDateDiffBusinessHours (
@date1 DATETIME,
@date2 DATETIME
) RETURNS DATETIME AS
BEGIN
DECLARE @sat INT
DECLARE @sun INT
DECLARE @workday_s INT
DECLARE @workday_e INT
DECLARE @basedate1 DATETIME
DECLARE @basedate2 DATETIME
DECLARE @calcdate1 DATETIME
DECLARE @calcdate2 DATETIME
DECLARE @iteratordate DATETIME
DECLARE @cworkdays INT
DECLARE @coffdays INT
DECLARE @returnval INT
SET @workday_s = 480 -- work day start: 8 hours
SET @workday_e = 1080 -- work day end: 18 hours
-- calculate Saturday and Sunday dependent on SET DATEFIRST option
SET @sat = CASE @@DATEFIRST WHEN 7 THEN 7 ELSE 7 - @@DATEFIRST END
SET @sun = CASE @@DATEFIRST WHEN 7 THEN 1 ELSE @sat + 1 END
SET @calcdate1 = @date1
SET @calcdate2 = @date2
-- @date1: assume next day if start was after end of workday
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) > @workday_e
THEN @basedate1 + 1
ELSE @calcdate1
END
-- @date1: if Saturday or Sunday, make it next Monday
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE DATEPART(dw, @basedate1)
WHEN @sat THEN @basedate1 + 2
WHEN @sun THEN @basedate1 + 1
ELSE @calcdate1
END
-- @date1: assume @workday_s as the minimum start time
SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) < @workday_s
THEN DATEADD(mi, @workday_s, @basedate1)
ELSE @calcdate1
END
-- @date2: assume previous day if end was before start of workday
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) < @workday_s
THEN DATEADD(mi, @workday_e, @basedate2 - 1)
ELSE @calcdate2
END
-- @date2: if Saturday or Sunday, make it previous Friday
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE DATEPART(dw, @calcdate2)
WHEN @sat THEN @basedate2 - 0.00001
WHEN @sun THEN @basedate2 - 1.00001
ELSE @date2
END
-- @date2: assume @workday_e as the maximum end time
SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) > @workday_e
THEN DATEADD(mi, @workday_e, @basedate2)
ELSE @calcdate2
END
-- count full work days (subtract Saturdays, Sundays and holidays)
SET @cworkdays = DATEDIFF(dd, @basedate1, @basedate2)
SET @iteratordate = @basedate1
SET @coffdays = 0
WHILE DATEDIFF(dd, @iteratordate, @basedate2) > 0
BEGIN
IF DATEPART(dw, @iteratordate) = @sat OR DATEPART(dw, @iteratordate) = @sun OR EXISTS (SELECT holidaydate FROM dbo.holidays_lu (NOLOCK) WHERE holidaydate = @iteratordate)
SET @coffdays = @coffdays + 1
SET @iteratordate = DATEADD(dd, 1, @iteratordate)
END
SET @cworkdays = @cworkdays - @coffdays
-- calculate effective duration in minutes
SET @returnval = @cworkdays * (@workday_e - @workday_s)
+ @workday_e - DATEDIFF(mi, @basedate1, @calcdate1)
+ DATEDIFF(mi, @basedate2, @calcdate2) - @workday_e
-- return duration as an offset in minutes from date 0
RETURN DATEADD(mi, @returnval, 0)
END
解决方案
While searching the internet I came across this solution:
How do I count the number of business days between two dates?
It's using a work calendar table, which you can adjust as you wish for marking working days (including holidays).
Counting the business hours would be querying for:
- working days between start and end date excluding both start and end date * (working hours/day)
- + working hours on start day (if it's a working day)
- + working hours on end day (if it's a working day)
Maybe it's possible to also code the business hours into the work calendar table, but I haven't tried it yet.
这篇关于考虑到周末和假期的两个日期之间的TSQL功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
