Calculate next business day for several years

Question

I have to generate a table with all non-business days and their following business day.

Given is: A table storing the weekends for certain cities.

CREATE TABLE WeekHolidays 
(
  CitiesId    int
 ,Cities_Name varchar(50)
 ,HolidayDate int
)

Another table holding public holidays:

CREATE TABLE Holidays
(
   CitiesId int
  ,Cities_Name varchar(50) 
  ,HolidayDate datetime
)

Currently i am generating the weekend days for the next 2047 weeks (almost 40 years) + adding the public holidays like:

    SELECT Cities_Id
          ,Cities_Name
          ,DATEADD(WEEK,sptv.number,DATEADD(DAY,(((HolidayDate - 1) + 7) %7),DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0))) as HolidayDate
      FROM WeekHolidays 
     CROSS JOIN master..spt_values sptv
      WHERE sptv.type = 'P' 
   UNION
    SELECT Cities_Id
          ,Cities_Name
          ,HolidayDate
      FROM Holidays

But now i need to add for each of those dates the next business day with the logik: rolling forward to the first day which is not in this table (for each city).

Rextester to reproduce: http://rextester.com/OZTT34904

So the result should be like:

CitiesId    Cities_Name   HolidayDate     NextBusinessDay
3           PARIS         14.07.2017      17.07.2017
3           PARIS         15.07.2017      17.07.2017
3           PARIS         16.07.2017      17.07.2017
3           PARIS         22.07.2017      24.07.2017
3           PARIS         23.07.2017      24.07.2017
3           PARIS         29.07.2017      31.07.2017
3           PARIS         30.07.2017      31.07.2017
3           PARIS         05.08.2017      07.08.2017
3           PARIS         06.08.2017      07.08.2017
3           PARIS         12.08.2017      14.08.2017
3           PARIS         13.08.2017      14.08.2017
3           PARIS         15.08.2017      16.08.2017
3           PARIS         18.08.2017      21.08.2017
3           PARIS         19.08.2017      21.08.2017
3           PARIS         20.08.2017      21.08.2017
3           PARIS         26.08.2017      28.08.2017
3           PARIS         27.08.2017      28.08.2017

In example: HolidayDate = 14.07.2017 the next BusinessDay will be 17.07.2017, as 15.07.&16.07. are contained in the HolidayDate column (saturday&sunday are holidays in Paris).

I hope its more understandable now.

Any idea how to approach this easily and fast?

Thanks in advance! Stefan

why distinguish between weekends and public holidays in separate tables. If it's a holiday, it's a holiday. If you really need to distinguish, just add a column HolidayType or something. That should simplify your structure at least. — ADyson, Aug 11 '17 at 15:04
[This will probably save you a ton of time](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) It computes weekdays, holidays, etc... you could edit Aaron's method slightly for your own holidays — S3S, Aug 11 '17 at 15:04
those tables are given by a system. i don't really like it, but i have to cope with it. its a legacy system — Esteban P., Aug 11 '17 at 15:05
@EstebanP. I'm a little confused. What are you expecting to get as a result? Can you show a sample desired result? — Siyual, Aug 11 '17 at 15:09
@Siyual of course. give me a second, i will add the expected result: — Esteban P., Aug 11 '17 at 15:10
@Siyual i updated my question also with a rextester and an expected result. — Esteban P., Aug 11 '17 at 15:44

score 0 · Answer 1 · answered Aug 11 '17 at 17:37

You can try this:

with getstarts as (
select mydays.*
    -- transform the dates to have weekends day as 6 and 7
    ,case when DATEPART(weekday, Holiday) =1 then 7
        else  DATEPART(weekday, Holiday)-1  end as weekdays
    FROM (
        YOUR_TABLE
    ) mydays
)

SELECT g.*, DATEADD(day,8-g.weekdays,g.Holiday) as c
FROM getstarts AS g 
where g.weekdays between 6 and 7
order by holiday

I am transforming the dates to have Monday being day 1 of the week. Then I am calculating the dates by adding the missing days

To create a table containing dates have a look at this nice inline table valued function https://stackoverflow.com/a/30133693/8302316

Calculate next business day for several years

1 Answers1