Join to Calendar Table - 5 Business Days

问题:

So this is somewhat of a common question on here but I haven't found an answer that really suits my specific needs. I have 2 tables. One has a list of ProjectClosedDates. The other table is a calendar table that goes through like 2025 which has columns for if the row date is a weekend day and also another column for is the date a holiday.

My end goal is to find out based on the ProjectClosedDate, what date is 5 business days post that date. My idea was that I was going to use the Calendar table and join it to itself so I could then insert a column into the calendar table that was 5 Business days away from the row-date. Then I was going to join the Project table to that table based on ProjectClosedDate = RowDate.

If I was just going to check the actual business-date table for one record, I could use this:

SELECT actual_date from 
(
    SELECT actual_date, ROW_NUMBER() OVER(ORDER BY actual_date) AS Row 
    FROM DateTable
    WHERE is_holiday= 0 and actual_date > '2013-12-01'
    ORDER BY actual_date
) X
WHERE row = 65

from here:

sql working days holidays

However, this is just one date and I need a column of dates based off of each row. Any thoughts of what the best way to do this would be? I'm using SQL-Server Management Studio.

回答1:

Completely untested and not thought through:

If the concept of "business days" is common and important in your system, you could add a column "Business Day Sequence" to your table. The column would be a simple unique sequence, incremented by one for every business day and null for every day not counting as a business day.

The data would look something like this:

Date       BDAY_SEQ
========== ========
2014-03-03    1
2014-03-04    2
2014-03-05    3
2014-03-06    4
2014-03-07    5
2014-03-08    
2014-03-09    
2014-03-10    6

Now it's a simple task to find the N:th business day from any date. You simply do a self join with the calendar table, adding the offset in the join condition.

select a.actual_date
      ,b.actual_date as nth_bussines_day
  from DateTable a
  join DateTable b on(
       b.bday_seq = a.bday_seq + 5
  );