如何将工作时间添加到约会时间

Question

我正在寻找一个函数，我可以定义工作时间例如上午9点至下午5点，然后将工作时间添加到datetime例如datetime 20121-08-05 16:30:00添加3个工作时间，因此日期时间将是20121-08-06 11:30:00。

我目前在工作日这样做，见下面的代码

这是用来计算任务上的SLA，例如任务在2021-08-05 16:30:00到达SLA是3个工作小时，所以它需要在2021-08-06 11:30:00之前完成。

如有任何帮助，不胜感激

感谢

declare 
@ReturnDate     datetime,
@StartDate      datetime,
@CountDays      int,
@Country        varchar (255)  
set @StartDate  =  '2021-08-05 16:30:00.000'
set @CountDays  = 2
set @Country    = 'England and Wales'
;with cteWorkingDates as
(
select        DAT.Calendar_Date_Date,
ROW_NUMBER ()    over    (
order by    DAT.Calendar_Date_Date
) as RowNo
from        dwBoyce.archway.Calendar_Date        DAT    with (nolock)
left join    dwBoyce.archway.GovUK_BankHolidays    BAH    with (nolock)    on    DAT.Calendar_Date_Date    = BAH.GovUK_BankHolidays_Date
      and    @Country                = BAH.GovUK_BankHolidays_Country
where        DAT.Calendar_Date_Date > @StartDate
and            DAT.Calendar_Date_WorkingDay = 1
and            BAH.GovUK_BankHolidays_Date is null
)
select     cast(wd.Calendar_Date_Date as datetime) + DATEADD(day, 0 - DATEDIFF(day, 0, @StartDate), @StartDate)
from    cteWorkingDates    WD
where    WD.RowNo    = @CountDays

Answer 1

您可以尝试以下操作:

计算，从创建票证开始，还剩下多少分钟的工作时间。然后计算SLA请求可能需要的分钟数(在您的示例中为3 * 60 = 180分钟)。当剩余工作时间小于SLA时间(分钟)时，应答时间为票务时间+ 1天+ (SLA分钟-剩余工作时间，分钟)

如下示例:

DECLARE @WorkingHoursStart TIME = '09:00:00.000';
DECLARE @WorkingHoursEnd TIME = '17:00:00.000';
DECLARE @WorkingHoursCount INT = DATEDIFF(HOUR, @WorkingHoursStart, @WorkingHoursEnd);
DECLARE @SLAHours INT = 32;
DECLARE @TicketTimestamp DATETIME = '2022-11-01 12:30:00';
DECLARE @TicketWorkingHoursStart DATETIME = DATETIMEFROMPARTS(YEAR(@TicketTimestamp)
,MONTH(@TicketTimestamp)
,DAY(@TicketTimestamp)
,DATEPART(HOUR, @WorkingHoursStart)
,DATEPART(MINUTE, @WorkingHoursStart)
,0
,0);
DECLARE @TicketWorkingHoursEnd DATETIME = DATETIMEFROMPARTS(YEAR(@TicketTimestamp)
,MONTH(@TicketTimestamp)
,DAY(@TicketTimestamp)
,DATEPART(HOUR, @WorkingHoursEnd)
,DATEPART(MINUTE, @WorkingHoursEnd)
,0
,0);
DECLARE @TicketWorkingTimestamp DATETIME = CASE
WHEN @TicketTimestamp < @TicketWorkingHoursStart
THEN @TicketWorkingHoursStart
WHEN @TicketTimestamp > @TicketWorkingHoursEnd
THEN DATEADD(d, 1, @TicketWorkingHoursStart)
ELSE @TicketTimestamp
END;
DECLARE @TmpTimeStamp DATETIME = DATEADD(d, @SLAHours/@WorkingHoursCount, @TicketWorkingTimestamp);
DECLARE @TmpSLAHours INT = @SLAHours - ((@SLAHours/@WorkingHoursCount)*@WorkingHoursCount);
DECLARE @TmpSLAMinutes INT = @TmpSLAHours * 60;
DECLARE @AnswerTimestamp DATETIME = CASE
WHEN DATEDIFF(MINUTE, CAST(@TmpTimeStamp AS TIME), @WorkingHoursEnd) < @TmpSLAMinutes
THEN DATEADD(MINUTE
,@TmpSLAMinutes - DATEDIFF(MINUTE
,CAST(@TmpTimeStamp AS TIME)
,@WorkingHoursEnd)
,DATEADD(DAY
,CASE WHEN @TmpSLAHours/@WorkingHoursCount = 0 THEN 1 ELSE 0 END
,DATETIMEFROMPARTS(YEAR(@TmpTimeStamp)
,MONTH(@TmpTimeStamp)
,DAY(@TmpTimeStamp)
,DATEPART(HOUR, @WorkingHoursStart)
,DATEPART(MINUTE, @WorkingHoursStart)
,DATEPART(SECOND, @TmpTimeStamp)
,0)
)
)
ELSE DATEADD(MINUTE, @TmpSLAMinutes, @TmpTimeStamp)
END
DECLARE @WeekEndDaySkip INT = ((DATEPART(WEEKDAY, @TicketTimestamp)+DATEDIFF(d, @TicketTimestamp, @AnswerTimestamp))/7)*2
SELECT @TicketTimestamp AS TicketTime
, @AnswerTimestamp AS AnswerTimestamp
, DATEADD(d, @WeekEndDaySkip, @AnswerTimestamp) AS AnswerTimestampWOWeekend

Answer 2

添加此功能处理跨越周末的sla

declare 
@date1 date = cast(@TicketTimestamp as date)
,@date2 date = cast(@TmpTimeStamp as date)
declare  @Count  int
Select @Count = count(*)
from archway.Calendar_Date d
where d.Calendar_Date_Date between @date1 and @date2
and Calendar_Date_DayName in ('Saturday','Sunday')
DECLARE @TmpTimeStamp1 DATETIME = case when @count = 2 then DATEADD(d,@count,@TmpTimeStamp)
else @TmpTimeStamp end