从SQL表中查找下一个工作日

I am dealing with the holiday table of a application and I have to find the next working days based on the holiday list in that table .

我正在处理应用程序的假期表，我必须根据该表中的假期列表找到下一个工作日。

If the input is a working day, we expect a blank/NULL to be returned, but if it is a holiday, we expect the next working day to be returned.

如果输入是工作日，我们希望返回空白/ NULL，但如果是假期，我们希望返回下一个工作日。

My holiday table contains below sample data. First date column is for startdate and second one is for enddate. Instead of using startdate and enddate for two consecutive holidays. Client have created two separate rows.

我的假期表包含以下样本数据。第一个日期列用于startdate，第二个用于enddate。而不是连续两个假期使用startdate和enddate。客户端已创建两个单独的行。

Now I have to write a select query which will give the next working days based on that sample data.

现在我必须编写一个select查询，它将根据该示例数据提供下一个工作日。

Suppose if I am passing '2016-04-20 00:00:00.000' as the conditional date then the query should return '2016-04-22 00:00:00.000' as the working date and there are consecutive two holidays.

假设我通过'2016-04-20 00：00：00.000'作为条件日期，则查询应返回'2016-04-22 00：00：00.000'作为工作日期，并且连续两个假期。

2016    2016-04-20 00:00:00.000 2016-04-20 00:00:00.000 Test
2016    2016-04-21 00:00:00.000 2016-04-21 00:00:00.000 Test2
2016    2016-04-28 00:00:00.000 2016-04-28 00:00:00.000 Test3

3 个解决方案

#1

You can try this:

你可以试试这个：

--create table holidays(y int, ds datetime, de datetime, hname varchar(10));
--insert into holidays  values
--(2016,'2016-04-20 00:00:00.000','2016-04-20 00:00:00.000','Test'),
--(2016,'2016-04-21 00:00:00.000','2016-04-21 00:00:00.000','Test2'),
--(2016,'2016-04-28 00:00:00.000','2016-04-28 00:00:00.000','Test3'),
--(2016,'2016-04-22 00:00:00.000','2016-04-22 00:00:00.000','Test4')
CREATE FUNCTION dbo.getNextDate(@dateToCheck datetime) RETURNS Datetime
AS
BEGIN

 RETURN(
select top 1 dateadd(d,1,de)  from 

(select y,
  MIN(ds) as ds, 
  MAX(ds) as de
from 
 (
  Select 
   *, 
   ROW_NUMBER() OVER(ORDER BY ds asc) as ranking 
   from holidays
 ) t  
 group by y,(CAST(ds AS INT)-Ranking)
)t
where @dateToCheck BETWEEN ds AND de
)
END

Upon testing:

经测试：

SELECT dbo.getNextDate('2016-04-23 00:00:00.000')-- returns NULL
SELECT dbo.getNextDate('2016-04-21 00:00:00.000')-- returns 2016-04-23 00:00:00.000

SQL demo link

SQL演示链接

#2

Let me know if the below mentioned code works.This code first makes a calendar table and then excludes Saturday and Sunday from the dates.

如果下面提到的代码有效，请告诉我。此代码首先创建一个日历表，然后从日期中排除星期六和星期日。

declare @mn date = (select min(yourdate) from table)
select top 1 a.caldate
from
(
select dateadd(dd,row_number() over (order by (select 1)) - 1,@mn) as caldate
from sys.all_objects
) as a
where a.caldate not in (select cast(yourdate as date) as yourdate from TableA) and datename(dw,a.caldate) not in ('Saturday','Sunday') and a.caldate >= '2016-04-20'

#3

Suposing a holidays table with this structure:

使用此结构设置假期表：

CREATE TABLE holidays (
    [year] int,
    [ds] datetime,
    [de] datetime,
    [description] nvarchar(50)
)

You can create a function that iterates through dates until it finds the correct one

您可以创建一个迭代日期的函数，直到找到正确的日期

CREATE FUNCTION dbo.getNextDate(@dateToCheck datetime) RETURNS Datetime
AS
BEGIN
    DECLARE @tempDate datetime
    SET @tempDate=DATEADD(day,1,@dateToCheck)
    WHILE EXISTS(SELECT * FROM holidays WHERE @tempDate BETWEEN ds AND de)
    BEGIN
        SET @tempDate=DATEADD(day,1,@tempDate)
    END

    RETURN @tempDate
END

This is a very rudimentary first aproximation but it should work.

这是一个非常基本的第一个近似，但它应该工作。

#1