在无循环的SQL中添加业务日期 [英] Add business days to date in SQL without loops

问题描述

我目前在我的SQL数据库中有一个功能，可以添加一定的工作日期，例如日期。如果您输入星期四并添加两天的日期，它将返回下一个星期一的日期。我没有打扰任何假期，只有周末被排除在外。

I currently have a function in my SQL database that adds a certain amount of business days to a date, e.g. if you enter a date that is a Thursday and add two days, it will return the date of the following Monday. I'm not bothered about any holidays, only weekends are excluded.

问题是，目前这是使用while循环完成的，而且似乎正在大大减慢生成表时使用它的存储过程。有没有人知道有没有办法在没有循环或游标的情况下执行这个计算？

The problem is that this is currently done using a while loop, and it appears to be massively slowing down the stored procedure that uses it while generating a table. Does anyone know if there is any way to perform this calculation without while loops or cursors?

只是为了信息，这是当前的功能：

Just for information, this is the current function:

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @toDate datetime
DECLARE @daysAdded integer

-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate

while @daysAdded <= @daysToAdd
begin
    -- add a day to the to date
    set @toDate = DateAdd(day, 1, @toDate)
    -- only move on a day if we've hit a week day
    if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
    begin
        set @daysAdded = @daysAdded + 1
    end
end

RETURN @toDate

END

推荐答案

自从原来的错误以来，这个答案已经被接受了，已经有了很大的改变。我对新查询更有信心，而且不依赖于 DATEFIRST

This answer has been significantly altered since it was accepted, since the original was wrong. I'm more confident in the new query though, and it doesn't depend on DATEFIRST

我认为这应该覆盖：

declare @fromDate datetime
declare @daysToAdd int

select @fromDate = '20130123',@DaysToAdd = 4

declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')

;with Numbers as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
    select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
    select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
    from
    Split t
        left join
    Numbers n
        on
            t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck

我们将一段时间的时间分成几周，一周之内。我们然后使用一个小数字表来解决，如果添加这几天将导致我们打到星期六。如果是，那么我们需要再增加2天。

We split the time to be added into a number of weeks and a number of days within a week. We then use a small numbers table to work out if adding those few days will result in us hitting a Saturday. If it does, then we need to add 2 more days onto the total.

这篇关于在无循环的SQL中添加业务日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！