在T-SQL中构建HR数据集，需要一个行开始日期列和一个行结束日期列

Question

我有人力资源数据集问题。对于一个职位，我有职位的详细信息，如类别、类型等。这些细节会随着时间的推移而变化，并且相互独立，从而产生以下数据集：

Position    StartDate      EndDate
1           '2020-01-01'   '3000-01-01'
Position    Type        StartDate     EndDate
1           'Part-time' '2020-01-01'  '2020-07-31'
1           'Full-time' '2020-08-01'  '2020-08-30'
1           'Part-time' '2020-09-01'  '3000-01-01
Position    Category      StartDate     EndDate
1           'Agency'      '2020-01-01'  '2020-06-30'
1           'Employee'    '2020-07-01'  '2020-08-30'
1           'Contractor'  '2020-09-01'  '3000-01-01

因此，我想从中获得一个数据集，例如：

Position   Type         Category      StartDate      EndDate
1          'Part-time'  'Agency'      '2020-01-01'   '2020-06-30'
1          'Part-time'  'Employee'    '2020-07-01'   '2020-07-31'
1          'Full-time'  'Employee'    '2020-08-01'   '2020-08-30'
1          'Part-time'  'Contractor'  '2020-09-01'   '3000-01-01'

因此，当在某个日期进行筛选时，它将带回该职位在该日期的情况。对于"2020-07-05"上的此示例，该职位将显示为兼职员工。

到目前为止，我研究的方法包括创建StartDate和EndDate字段的CASE语句

创建数据集的并集，并运行ROW_NUMBER((OVER(PARTITION BY Position OVER BY StartDate，EndDate(，试图在连接这些表时过滤掉错误的行。

编辑：很抱歉，我应该补充一下，这是问题的最小例子。我正在看的示例将有6-12列。因此，该解决方案需要具有可扩展性。

Answer 1

我选择的方法是在Types和Categories表之间应用CROSS JOIN，通过两个表的重叠阶段进行过滤。

SELECT * FROM Position p
INNER JOIN Type t
ON t.Position = p.Position
CROSS JOIN Category c
WHERE c.StartDate >= t.StartDate AND c.StartDate <= t.EndDate
OR (t.StartDate >= c.StartDate AND t.StartDate <= c.EndDate)
OR (c.StartDate < t.StartDate AND c.EndDate > t.EndDate)
ORDER BY t.StartDate

在SELECT列表中，您可以应用CASE语句为您的日期范围选择正确的日期。