加入日历表-5个工作日 [英] Join to Calendar Table - 5 Business Days

问题描述

所以这是这里的一个常见问题，但我没有找到真正适合我特定需求的答案。我有2张桌子。其中一个列出了ProjectClosedDates。另一个表是日历表，其运行时间类似于2025年，其中有列用于表示行日期是否是周末，而另一列是用于表示假日日期。

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.

我的最终目标是根据ProjectClosedDate找出该日期之后5个工作日的日期。我的想法是，我将使用日历表并将其连接到自身，因此可以在距行日期5个工作日的日历表中插入一列。然后，我将基于ProjectClosedDate = RowDate将Project表连接到该表。

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

从此处：

sql工作日假期

不过，这只是一个日期，我需要根据每一行创建一列日期。是否有想到最佳方法的想法？我正在使用SQL-Server Management Studio。

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.

推荐答案

完全未经测试，没有经过仔细考虑：

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.

数据看起来像这样：

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

现在，从任何日期查找第N个工作日是一项简单的任务。
您只需对日历表进行自联接，并在联接条件中添加偏移量。

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
  );

这篇关于加入日历表-5个工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！