SQL Server 如何将工作时间添加到日期时间

s4n0splo 于 2022-11-21 发布在其他

关注(0)|答案(2)|浏览(152)

我正在寻找一个功能，我可以定义工作时间，如上午9点-下午5点，然后添加工作时间的日期时间，如日期时间2021-08-05 16：30：00添加3个工作时间，使日期时间将是2021-08-06 11：30：00。
我目前在工作日执行此操作，请参见下面的代码
这是为了计算任务的SLA，例如，任务在2021年8月5日16：30：00到来，SLA为3个工作小时，因此需要在2021年8月6日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

sql-server

来源：https://stackoverflow.com/questions/68561929/how-can-you-add-working-hours-to-date-time

2条答案

按热度按时间

zpqajqem1#

您可能需要尝试以下操作：
评估从创建票证开始的剩余工作时间（分钟）。然后计算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

赞(0）回复(0）举报 2022-11-21

sy5wg1nm2#

将此交易添加到跨周末的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

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 如何将工作时间添加到日期时间

2条答案

相关问题

热门标签

最新问答