sql - 加入日期范围查询

Question

嗨
我有以下表格

=========================
Periods
=========================
PeriodID  StartDate   EndDate
1         01-01-11    07-01-11
2         08-01-11    15-01-11

全年以此类推

=========================
History
=========================
PersonID  From       To
1         01-01-11  05-04-11
2         17-06-11   NULL 
and so on

我想要以下输出

 StartDate   EndDate   PersonID
  01-01-11    07-01-11    1
  08-01-11    15-01-11    1
  .
  .
  15-04-11   21-04-11     NULL
  .
  .
  15-06-11   21-06-11     2

我需要在这两个表之间进行连接，但我不知道连接条件会是什么样子

拉加德

Answer 1

SELECT
  p.StartDate,
  p.EndDate,
  h.PersonID
FROM Periods p
  LEFT JOIN History h
    ON h.[From] BETWEEN p.StartDate AND p.EndDate OR
       p.StartDate BETWEEN h.[From] AND ISNULL(h.[To], '30000101')