如何从SQL中分配的日期计算业务日期和时间 [英] How can I calculate business date and time from date assigned in SQL

问题描述

例如，我们的日期为5/4/2017 01:00:00 PM，工作时间为工作日08:00至17:30，我应如何计算SLA日期和时间?

For example we have date 5/4/2017 01:00:00 PM And we have working hours 08:00 to 17:30 in weekdays How should I calculate SLA Date and Time ?

分配日期:5/5/2017 01:00:00 PM

Date Assigned :5/4/2017 01:00:00 PM

如果SLA是从分配日期开始的8个小时.

If SLA is 8 Hours from date assigned .

到期日期应类似于:5/5/2017 11:30:00 AM，不包括周末和非工作时间.

The Expiry Date should be Like : 5/5/2017 11:30:00 AM by excluding weekends and non-business hours.

推荐答案

这是一个更为复杂的问题，我想您应该为此给予赞赏.您需要考虑很多时间和日期，这些时间和日期超出了工作时间.这将包括任何假期，半天，办公室关闭等.最好通过维护被认为是工作时间的日期，小时甚至分钟表来解决.

This is a much more complicated problem then I think you are giving it credit for. There are a number of days and times that will fall outside of working hours that you will need to take into account. This will include any holidays, half-days, office closures, etc. This is best dealt with by maintaining a table of dates, hours or even minutes that are considered working time.

如果您不只是想要一个表，可以使用简单的 Dates 表以及第二个规则表，这些规则将何时将特定日期或时间视为工作时间或不允许您导出此值.

If you don't want a table just for this, a simple Dates table coupled with a second table of rules of when a particular date or time is considered working time or not will allow you to derive this.

如果您什至没有，则每次运行查询时都需要派生表，并包括您的工作中的 all 查询中的时间规则.创建 datetime 值的表的最有效方法是Tally表，在您的情况下，可以使用以下表:

If you don't even have that, you will need to derive your table each time you want to run your query and include all of your working time rules within this query. The most efficient way to create a table of datetime values is a Tally Table, which in your case can be utilised as follows:

declare @DateAssigned datetime = '05-04-2017 13:00:00';
declare @DayStart time = '08:00:00';
declare @DayEnd time = '17:30:00';
declare @SLAMinutes int = 480; -- 8 hours * 60 minutes per hour


       -- cte to create a table with 10 rows in
with n(n) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1)
       -- cte to cross join the rows together, resulting in 10^6 (1 million) rows.  Add or remove joins per number of minutes required.
       -- Use the row_number function to add an incremental number of minutes to the original @DateAssigned to get a table of minutes from the @DateAssigned value.
    ,t(t) as (select dateadd(minute,row_number() over(order by (select null))-1,@DateAssigned) from n n1, n n2, n n3, n n4, n n5, n n6)
       -- Select the first @SLANumber number of rows that meet your Working Time criteria.  We add 2 to this value do resolve the fencepost problem.
    ,d(d) as (select top (@SLAMinutes + 2) t
              from t
              where cast(t as time) >= @DayStart      -- Only return minutes from 08:00 onwards.
                and cast(t as time) <= @DayEnd        -- Only return minutes up to 17:30.
                and datepart(weekday,t) not in (7,1)  -- Only return minutes not on Saturday or Sunday.
              order by t)
  -- The MAX value in the cte d will be the last minute of your SLA window.
select @DateAssigned as DateAssigned
      ,max(d) as SLADeadline
from d;

哪些输出:

+-------------------------+-------------------------+
|      DateAssigned       |       SLADeadline       |
+-------------------------+-------------------------+
| 2017-05-04 13:00:00.000 | 2017-05-05 11:30:00.000 |
+-------------------------+-------------------------+

这篇关于如何从SQL中分配的日期计算业务日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！