选择与内部联接中的日期范围相对应的行

Question

我在一个表(表A(中有数据，看起来像：

id      datestart        dateend         department
1       2008-01-01       9999-12-31      fmcg
2       2010-02-01       2011-04-09      sales
.       .                .               .
.       .                .               .

有数百万行，9999-12-31表示当前日期。我有另一张表(表B(，看起来像：

yearmonth      id 
2010-01-01     1  
2010-01-01     2  
2010-02-01     1  
2010-02-01     2  
.              . 
.              .

表A中的id可以有多行，例如，id 2在销售中，但还有另一行看起来像：

id      datestart        dateend         department
2       2011-04-10       9999-12-31      fmcg

这意味着他们在销售后转到了fmcg(没有日期重叠(我想做的是在表B中添加另一列，显示员工在一个月的工作地点：

yearmonth      id        department
2010-01-01     1         fmcg
2010-01-01     2         NULL
2010-02-01     1         fmcg
2010-02-01     2         sales
.              . 
.              .
2011-05-01     1         fmcg
2015-05-01     2         fmcg

抱歉，我无法发布我使用过的确切代码，因为它在我的工作电脑上，我目前使用的是另一台电脑，但它看起来类似于：

select a.*
     , case when b.dateend = '9999-12-31' then b.department
       when a.yearmonth < b.dateend then b.department else b.department end as department
from table_a a
inner join table_b b on a.id = b.id
(apologies if missing crucial bits, am going by memory)

我知道这个代码不起作用，因为我得到了类似的东西：

yearmonth      id        department
2010-01-01     1         fmcg
2010-01-01     2         NULL
2010-02-01     1         fmcg
2010-02-01     2         sales
2010-02-01     2         fmcg
.              . 
.              .
2011-05-01     1         fmcg
2015-05-01     2         fmcg
2015-05-01     2         sales

但我很难理解什么是有效的。如果你能帮我，我将不胜感激！

Answer 1

试试这个：

SELECT B.ID, B.YearMonth, ApplyResult.Department FROM TableB B
OUTER APPLY(
    SELECT A.Department FROM TableA A 
        WHERE A.ID = B.ID AND B.YearMonth BETWEEN A.DateStart AND A.DateEnd
) ApplyResult

或者这个：

SELECT B.ID, B.YearMonth, A.Department FROM TableB B
LEFT OUTER JOIN TableA A On A.ID = B.ID AND B.YearMonth BETWEEN A.DateStart AND A.DateEnd

表A:

ID  DateStart   DateEnd     Department
1   2008-01-01  9999-12-31   DepA
2   2010-02-01  2010-03-09   DepA
2   2010-03-10  9999-12-31   DepB

表B:

ID  YearMonth
1   2010-01-01
2   2010-01-01
1   2010-02-01
2   2010-02-01
1   2010-03-01
2   2010-03-01
1   2010-04-01
2   2010-04-01

输出：

ID  YearMonth   Department
1   2010-01-01   DepA
2   2010-01-01   NULL
1   2010-02-01   DepA
2   2010-02-01   DepA
1   2010-03-01   DepA
2   2010-03-01   DepA
1   2010-04-01   DepA
2   2010-04-01   DepB

Answer 2

只需将日期条件添加到on子句：

select a.*, b.department 
from table_a a inner join
     table_b b
     on a.id = b.id and
        b.yearmonth between a.datestart and a.dateend