SQL Server功能将工作日添加到日期 [英] SQL Server function to add working days to a date

问题描述

我已经看到关于如何创建一个SQL Server函数的一些帖子，该函数将添加给定的工作日数。但是，他们没有一个完全按照我需要的方式计算。我们有一个目前在代码中完成的功能，但是我想移动到本机SQL Server函数（由存储过程和查询使用）。在我们开始评估存储费用之前，我们给我们的客户5个工作日收集货物。 5个工作日不包括周末和假期（我们有一个休假日期表）。这里的诀窍是，我需要在5个工作日之后立即得到日期，无论是周末还是假期。所以我需要该函数返回上一个工作日期，而不是第一个工作日。所以，例如：

I have seen a number of postings regarding how to create an SQL Server function that will add a given number of working days to a date. But, none of them calculate exactly the way I need. We have a function that is currently done in code but that I want to move to a native SQL Server function (for use by stored procedures and in queries). We give our customers 5 working days to collect cargo before we start assessing storage charges. The 5 working days exclude weekends and holidays (we have a table with the holiday dates). The trick here is that I need to get the date immediately after the 5 working days regardless of whether it is a weekend or holiday. So I need the function to return the last working date, NOT the first working day after. So, for example:

Oct 20th (Sat) plus 5 working days = Oct 26th (Fri)
Oct 21st (Sun) plus 5 working days = Oct 26th (Fri)
Oct 22nd (Mon) plus 5 working days = Oct 29th (Mon)
May 19th (Sat) plus 5 working days with May 21st a holiday = May 28th

5个工作日是目前的配发，但这可能会在将来发生变化工作日数需要作为参数。此外，该函数可能在相当大的数据集上使用，所以我宁愿在没有循环的情况下执行此操作。我们正在运行SQL Server 2008。

The 5 working days is the current allotment, but this may change in the future so the number of working days needs to be a parameter. Also, the function may be used over fairly large datasets so I would prefer doing this without loops. We are running SQL Server 2008.

编辑：这不是添加在SQL无循环中的业务日期，因为他们希望结束日期是一个工作日。我希望我的结束日期是在最后一个宽限期之后的任何日期（即：5个工作日周一至周五，我想要返回的星期六日期，而不是下个星期一）。

This is not a duplicate of "Add business days to date in SQL without loops" as they want the ending date to be a working day. I want my ending date to be whatever date immediately follows the last grace day (ie: 5 working days Mon to Fri I want the Sat date returned, NOT the following Mon).

推荐答案

create table holidays (
  date date);
GO

create function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
  select thedate
  from (
  select thedate=dateadd(d,v.day,cast(@date as date)),
         rn=row_number() over (order by v.day)
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where h.date is null and left(datename(dw,dateadd(d,v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn
  )
end
GO

除非你有很长的假期，10天应该足以找到下一个工作日的第五天。如果需要，请增加。

Unless you have long holidays, 10 days should be enough to find the 5th next working day. Increase it if you need to.

如果您需要从日期起更多的营业日，您可以使用这一天，这将适合一年或三年。 p>

If you need a larger number of business days from a date, you can use this which will cater for a year or three.

alter function dbo.findWorkDayAfter(@date datetime, @days int)
returns date as
begin
return (
  select thedate
  from (
  select thedate=dateadd(d,v.number,cast(@date as date)),
         rn=row_number() over (order by v.number)
  from master..spt_values v
  left join holidays h on h.date = dateadd(d,v.number,cast(@date as date))
  where h.date is null and left(datename(dw,dateadd(d,v.number,cast(@date as date))),1) <> 'S'
    and v.number >= 1 and v.type='p'
  ) x
  where @days = rn
  )
end
GO

这篇关于SQL Server功能将工作日添加到日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！