文章/答案/技术大牛

发布

社区首页 >问答首页 >以薄荷为单位，考虑到自定义的银行假日、维护日和工作时间

问以薄荷为单位，考虑到自定义的银行假日、维护日和工作时间
EN

Stack Overflow用户

提问于 2018-12-01 18:33:00

回答 2查看 124关注 0票数 1

我有一个表(table1)，它包括[deadline]和[completion]列，这两个列都是数据类型datetime。

我需要计算[deadline]和[completion]之间的日期差异(分钟)。

例如：

截止日期= 2018-11-22 09:05
完成= 2018-11-22 9:10

结果需要5。

但是，我还需要使用以前生成的自定义表(比如[tableBH] )来考虑银行假期，该表具有[date] [bank holiday name]。

另外，还有另一个表--带有[date] [maintenance reason]列的维护表[date] [maintenance reason]。

最后，我周一到周五的工作时间是0900到1900，星期六是1100 - 1500。

我需要的日期，理想的分钟，考虑到非工作时间，非工作日，和银行假日。

理想的方式是通过一个函数。有人能指点从哪里开始吗？

我的当前脚本只执行DATEDIFF ( mi , deadline, completion)

嗯，我是这么说的，但目前有点复杂，但我可以以后再处理。

为了提高透明度，我目前的代码(不考虑维护天数、银行假期和非工作时间)是：

CONVERT(varchar, Datediff(n, [deadline],[completion])/60) + ':' + Right('0' + CONVERT(varchar,(datediff(n,[deadline],[completion]) %60)),2)) as [TimeDIFF]

这种格式并不重要。

重要的是引用

定制银行假日桌，而不是sys，
定制维护表，
工作时间(星期六至工作日不同)
传递一个函数(在Server 2017中)

示例：

TableBH -列日期，银行假日名称

26 November 2018, FakeBH1

TableMaintenance -列日期、维护原因

28 November 2018, FakeMaintenance1

table1 -列id、截止日期、完成

1, 2018-11-24 14:55, 2018-11-27 09:05 
2, 2018-11-24 15:05, 2018-11-27 09:04
3, 2018-11-27 11:00, 2018-11-27 10:35
4, 2018-11-27 16:50, 2018-11-29 09:10

输出id，持续时间1= 10 2=4 3= -25 4= 20

sql

sql-server

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-12-11 19:36:56

BankDate    BankTimeStart       BankTimeEnd         WorkingMins
2018-11-19  09:00:00.0000000    19:00:00.0000000    720
2018-11-20  09:00:00.0000000    19:00:00.0000000    720
2018-11-21  09:00:00.0000000    19:00:00.0000000    720
2018-11-22  09:00:00.0000000    19:00:00.0000000    720
2018-11-23  09:00:00.0000000    19:00:00.0000000    720
2018-11-24  09:00:00.0000000    19:00:00.0000000    480
2018-11-25  09:00:00.0000000    19:00:00.0000000    480
2018-11-26  09:00:00.0000000    19:00:00.0000000    480 *(Bank Holiday)*
2018-11-27  09:00:00.0000000    19:00:00.0000000    720
2018-11-28  00:00:00.0000000    00:00:00.0000000    0 *(fakeMaintenance)*
2018-11-29  09:00:00.0000000    19:00:00.0000000    720
2018-11-30  09:00:00.0000000    19:00:00.0000000    720

上表受@donPablo启发

实际上，银行假日和星期日现在是上午9点到下午5点

还有两个额外的列IsBankHoliday和IsNonWorkingDay。

然后，我在DimDate过程中添加了以下内容：

CASE 
    WHEN [nw].[NonWorkingDayDate] IS  NOT NULL
        THEN '00:00:00'
    ELSE '09:00:00'
END AS [StartTime],


CASE 
    WHEN [nw].[NonWorkingDayDate] IS  NOT NULL
        THEN '00:00:00'
    WHEN [bh].[BankHolidayDate] IS  NOT NULL or [DayOfWeekNumber] IN (6,7)
        THEN '17:00:00'
    ELSE '21:00:00'
END AS [EndTime],

然后，我创建了以下函数：

ALTER FUNCTION [Udf].[GenerateBreachTime] (@CompletionAt [datetime],

@截止日期)

SELECT (SELECT
  CASE
    WHEN @Deadline > @CompletionAt THEN -1
    WHEN CONVERT(varchar(8), @Deadline, 112) <= CONVERT(varchar(8), @CompletionAt, 112) THEN CASE   --NWD DC|| ||DC  = 0
        WHEN ([Non Working Day] = 1 AND
          CONVERT(varchar(8), @Deadline, 112) = CONVERT(varchar(8), [DateId], 112)) --nwd
          OR @Deadline > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) --deadline after hours
          OR @CompletionAt < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) THEN 0
        -- D|C|
        WHEN @Deadline < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), @CompletionAt)
        --D||C
        WHEN @Deadline < CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        -- |DC|
        WHEN @Deadline >= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt <= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, @Deadline, @CompletionAt)
        --|D|C
        WHEN @Deadline >= CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) AND
          @CompletionAt > CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime) THEN DATEDIFF(n, @Deadline, CAST(Concat(CONVERT(date, @Deadline), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        ELSE 0
      END
    ELSE 0
  END
  AS [BreachMins]
FROM [Vw].[Dim Date] DimDate
WHERE dateid = CONVERT(varchar(8), @Deadline, 112))
+ (SELECT
  CASE
    WHEN CONVERT(varchar(8), @Deadline, 112) >= CONVERT(varchar(8), @CompletionAt, 112) THEN 0
    WHEN CONVERT(varchar(8), @Deadline, 112) <= CONVERT(varchar(8), @CompletionAt, 112) THEN CASE
        -- C|| OR NWD
        WHEN @CompletionAt < CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime) OR
          ([Non Working Day] = 1 AND
          CONVERT(varchar(8), @CompletionAt, 112) = CONVERT(varchar(8), [DateId], 112)) THEN 0
        -- |C|
        WHEN @CompletionAt <= CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime)
        --THEN 1
        THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), @CompletionAt)
        --||C
        WHEN @CompletionAt > CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime)
        --THEN 2
        THEN DATEDIFF(n, CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [StartTime], 108)) AS datetime), CAST(Concat(CONVERT(date, @CompletionAt), ' ', CONVERT(char(5), [EndTime], 108)) AS datetime))
        ELSE 0
      END
    ELSE 0
  END
  AS [BreachMins]
FROM [Vw].[Dim Date] DimDate
WHERE dateid = CONVERT(varchar(8), @CompletionAt, 112))
+ (SELECT
  CASE
    WHEN SUM(workingminsinday) IS NULL THEN 0
    ELSE SUM(workingminsinday)
  END AS [x]
FROM vw.[Dim Date]
WHERE dateid BETWEEN CONVERT(varchar(8), @Deadline + 1, 112) AND CONVERT(varchar(8), @CompletionAt - 1, 112))

AS [BreachMins]

日常过程调用函数。

        ,[BreachTime].[BreachInMins]
    FROM [Syn].[X]

      CROSS APPLY [udf].[GenerateBreachTime] ([Completed],[Deadline]) [BreachTime]

随后的视图处理-1种情况，将它们更改为NULL。

我希望这能帮助到其他人，如果不是完全清楚的话，我也会道歉。

票数 0

Stack Overflow用户

发布于 2018-12-03 06:30:17

创建Bank_Calendar表，其中列出了每个银行日期，并重点关注工作时间。它包括关闭，假日和维护与零时间。

BankDate    BankTimeStart       BankTimeEnd         BankDateDescription
2018-11-19  09:00:00.0000000    19:00:00.0000000    Mon
2018-11-20  09:00:00.0000000    19:00:00.0000000    Tue
2018-11-21  09:00:00.0000000    19:00:00.0000000    Wed
2018-11-22  09:00:00.0000000    19:00:00.0000000    Thu
2018-11-23  09:00:00.0000000    19:00:00.0000000    Fri
2018-11-24  11:00:00.0000000    15:00:00.0000000    Sat
2018-11-25  00:00:00.0000000    00:00:00.0000000    Sun closed
2018-11-26  00:00:00.0000000    00:00:00.0000000    Mon fakeBH1
2018-11-27  09:00:00.0000000    19:00:00.0000000    Tue
2018-11-28  00:00:00.0000000    00:00:00.0000000    Wed fakeMaintenance
2018-11-29  09:00:00.0000000    19:00:00.0000000    Thu
2018-11-30  09:00:00.0000000    19:00:00.0000000    Fri

下面是我对table1的解释，它将日期字段与时间字段分开，从而使以后的算法更容易实现。

DC_ID   Deadline_Date   Deadline_Time   Completion_Date Completion_Time
    1   2018-11-24  14:55:00.0000000    2018-11-27  09:05:00.0000000
    2   2018-11-24  15:05:00.0000000    2018-11-27  09:04:00.0000000
    3   2018-11-27  11:00:00.0000000    2018-11-27  10:35:00.0000000
    4   2018-11-27  16:50:00.0000000    2018-11-29  09:10:00.0000000
    5   2018-11-22  09:05:00.0000000    2018-11-22  09:10:00.0000000

这是sql

select
    dc.*
    ,sum(
    Case when dc.Deadline_Date = cal.BankDate and dc.Completion_Date = cal.BankDate Then 
             --'SameDay'
             Datediff(n, dc.Deadline_Time, dc.Completion_Time)
          when dc.Deadline_Date = cal.BankDate and dc.Completion_Date <> cal.BankDate 
          and  dc.Deadline_Time > cal.BankTimeEnd Then
             --'FR' but Deadline is after Closing
             0 
          when dc.Deadline_Date = cal.BankDate and dc.Completion_Date <> cal.BankDate Then 
             --'FR' the remaining part of the day until closing
             Datediff(n, dc.Deadline_Time, cal.BankTimeEnd)
          when dc.Deadline_Date <> cal.BankDate and dc.Completion_Date <> cal.BankDate Then 
             --'allday' add the entire day  (for closings/holidays it becomes zero)
             Datediff(n, cal.BankTimeStart, cal.BankTimeEnd)
          when dc.Deadline_Date <> cal.BankDate and dc.Completion_Date = cal.BankDate Then 
             --'TO' add the beginning part of the day
             Datediff(n, cal.BankTimeStart, dc.Completion_Time)
      End )
      as minnn
from DateDiff_DeadlineCompletion as dc
Left Join DateDiff_Calendar as cal

On dc.Deadline_Date <= cal.BankDate
  and dc.Completion_Date >= cal.BankDate

Group By dc.DC_ID
         ,dc.Deadline_Date
         ,dc.Deadline_Time
         ,dc.Completion_Date
         ,dc.Completion_Time

结果(顺便说一句，我们得到了第4项的不同答案)

DC_ID   Deadline_Date   Deadline_Time   Completion_Date Completion_Time minnn
1   2018-11-24  14:55:00.0000000    2018-11-27  09:05:00.0000000    10
2   2018-11-24  15:05:00.0000000    2018-11-27  09:04:00.0000000    4
3   2018-11-27  11:00:00.0000000    2018-11-27  10:35:00.0000000    -25
4   2018-11-27  16:50:00.0000000    2018-11-29  09:10:00.0000000    140
5   2018-11-22  09:05:00.0000000    2018-11-22  09:10:00.0000000    5

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/53573855

复制

相似问题

问以薄荷为单位，考虑到自定义的银行假日、维护日和工作时间
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问以薄荷为单位，考虑到自定义的银行假日、维护日和工作时间EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问以薄荷为单位，考虑到自定义的银行假日、维护日和工作时间
EN