sql工作日假期 [英] sql working days holidays

问题描述

我见过用于其他编码语言(Python、jQuery 等)但没有用于 SQL 的此类函数的不同版本.我有一个程序需要计算自创建日期起 65 天的日期，但它不能包括周末或假期.我们已经有一个功能，可以只将工作日添加到日期，但不考虑假期.我们有一个假期表，其中列出了所有假期日期 tblHolidayDates，其中包含一个以标准日期格式显示的 HolidayDate 列.

I've seen different version of this kind of function for other coding languages (Python, jQuery, etc.) but not for SQL. I have a procedure that needs to have a date calculated that is 65 days from the creation date, but it cannot include weekend or holidays. We already have a function that is able to add only working days to a date, but not take into account holidays. We have a holiday table that lists all the holiday dates, tblHolidayDates with a column HolidayDate in standard date format.

我该怎么做?如果有人可以给我一个 CREATE TABLE 查询，我也会考虑可能只是创建一个日历表 - 它只需要日期、工作日和假日列.

How would I do this? I'd also consider maybe just creating a Calendar table as well if someone could give me a CREATE TABLE query for that - all it would need is dates, weekday, and holiday columns.

下面我给出了增加工作日的当前循环函数，但它缺少假期.任何帮助将不胜感激！

Below I have given the current loop function that adds business days, but it's missing holidays. Any help would be greatly appreciated!!

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

推荐答案

我在几年前写了一个 SQL 函数来构建一个动态假日表作为表函数.链接如下:

I wrote a SQL function years ago to build a dynamic holiday table as a table function. The link is below:

http://www.joebooth-consulting.com/sqlServer/sqlServer.html#CalendFunc

希望能帮到你...

您可以通过如下的 SQL 语句访问表函数(或您自己的假期表)来确定假期数

You can access the table function (or your own holiday table) to determine number of holidays via a SQL statement like below

SELECT count(*) FROM holiday_date(2013)
WHERE holiday_date BETWEEN @fromDate AND @toDate

然后使用 dateAdd() 将计数添加到返回的日期.

then add the count to the returned date using dateAdd().

如果您可能在周末有假期，请将以下内容添加到 WHERE 子句中

If you might have holidays that fall on a weekend, add the following to the WHERE clause

   AND DatePart(dw, Holiday_date) != 1) and (DatePart(dw, holiday_date) != 7)

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