我有5张桌子，每个桌子都有自己独立的有效日期范围。我想合并所有5个表格，并且只返回所有5个日期跨度都同时生效的组合吗？我只用了3张桌子就可以使这项工作规模较小。但这确实很难看而且很难读。我希望有人对SQL有更好的了解，然后可以帮助我找到一个更优雅的解决方案。
我正在使用SQL Server 2016，术语日期中的null表示开放式，也可以替换12/31/2099。我最终希望在SQL视图中使用查询与其他数据联接，因此存储过程不会有太大帮助。
期望的输出
ClientID, LocationCode, FamilyID, DependentID, CoverageCode, EffDate, TermDate
这是DDL / DML SQL。
declare @EmployeeLocation table ( ClientID int, FamilyID int, LocationCode varchar(3), EffDate date, TermDate date)
insert into @EmployeeLocation values (10000, 1000, '003', '1/1/2016', '1/1/2020')
insert into @EmployeeLocation values (10000, 1000, '006', '1/1/2020', NULL)
insert into @EmployeeLocation values (10000, 2000, '006', '1/1/2016', '1/1/2020')
insert into @EmployeeLocation values (10000, 2000, '003', '1/1/2020', NULL)
declare @Employees table( ClientID int, FamilyID int, EffDate date, TermDate date)
insert into @Employees values (10000, 1000, '1/1/2017', NULL)
insert into @Employees values (10000, 2000, '1/1/2017', '3/1/2020')
declare @Dependents table( ClientID int, FamilyID int, DependentID int, EffDate date, TermDate date)
insert into @Dependents values (10000, 1000, 1, '1/1/2017', NULL)
insert into @Dependents values (10000, 1000, 2, '6/1/2017', '3/1/2020')
insert into @Dependents values (10000, 2000, 1, '1/1/2017', NULL)
insert into @Dependents values (10000, 2000, 2, '1/1/2018', '6/1/2019')
insert into @Dependents values (10000, 2000, 3, '1/1/2019', NULL)
declare @ClientCoverage table( ClientID int, LocationCode varchar(3), CoverageCode varchar(2), EffDate date, TermDate date)
insert into @ClientCoverage values (10000, '003', 'AA', '1/1/2016', '1/1/2017')
insert into @ClientCoverage values (10000, '003', 'AB', '1/1/2017', '1/1/2018')
insert into @ClientCoverage values (10000, '003', 'AC', '1/1/2018', NULL)
insert into @ClientCoverage values (10000, '006', 'AB', '1/1/2017', '1/1/2018')
insert into @ClientCoverage values (10000, '006', 'AC', '1/1/2018', '1/1/2019')
insert into @ClientCoverage values (10000, '006', 'AD', '1/1/2019', NULL)
declare @EmployeeCoverage table( ClientID int, FamilyID int, CoverageCode varchar(2), EffDate date, TermDate date)
insert into @EmployeeCoverage values(10000, 1000, 'AA', '1/1/2017', '1/1/2018')
insert into @EmployeeCoverage values(10000, 1000, 'AB', '1/1/2017', '1/1/2019')
insert into @EmployeeCoverage values(10000, 1000, 'AC', '1/1/2019', '1/1/2020')
insert into @EmployeeCoverage values(10000, 1000, 'AA', '1/1/2020', NULL)
insert into @EmployeeCoverage values(10000, 2000, 'AB', '1/1/2017', '1/1/2018')
insert into @EmployeeCoverage values(10000, 2000, 'AC', '1/1/2017', '1/1/2019')
insert into @EmployeeCoverage values(10000, 2000, 'AD', '1/1/2020', NULL)
select *
from @employees t1
inner join @employeelocation t2 on t1.clientId = t2.clientid and t1.FamilyID = t2.FamilyID
inner join @dependents t3 on t1.clientid = t3.clientid and t1.FamilyID = t3.FamilyID
inner join @ClientCoverage t4 on t1.clientid = t4.clientid and t2.locationcode = t4.locationcode
inner join @EmployeeCoverage t5 on t1.clientid = t5.clientid and t1.FamilyID = t5.FamilyID and t4.coveragecode = t5.coveragecode
当前的丑陋解决方案-进行中
select t1.ClientID, t1.FamilyID, t3.DependentID, t2.LocationCode, t4.CoverageCode
, (select max(v) from (values (t1.effdate),(t2.effdate),(t3.effdate),(t4.effdate),(t5.effdate)) as value(v)) as eff
from @employees t1
inner join @employeelocation t2 on t1.clientId = t2.clientid and t1.FamilyID = t2.FamilyID
inner join @dependents t3 on t1.clientid = t3.clientid and t1.FamilyID = t3.FamilyID
inner join @ClientCoverage t4 on t1.clientid = t4.clientid and t2.locationcode = t4.locationcode
inner join @EmployeeCoverage t5 on t1.clientid = t5.clientid and t1.FamilyID = t5.FamilyID and t4.coveragecode = t5.coveragecode
where t1.EffDate < isnull(t2.TermDate, '2020-12-31')
and t1.EffDate < isnull(t3.TermDate, '2020-12-31')
and t1.EffDate < isnull(t4.TermDate, '2020-12-31')
and t1.EffDate < isnull(t5.TermDate, '2020-12-31')
and t2.EffDate < isnull(t1.TermDate, '2020-12-31')
and t3.EffDate < isnull(t1.TermDate, '2020-12-31')
and t4.EffDate < isnull(t1.TermDate, '2020-12-31')
and t5.EffDate < isnull(t1.TermDate, '2020-12-31')
and t2.EffDate < isnull(t3.TermDate, '2020-12-31')
and t2.EffDate < isnull(t4.TermDate, '2020-12-31')
and t2.EffDate < isnull(t5.TermDate, '2020-12-31')
and t3.EffDate < isnull(t2.TermDate, '2020-12-31')
and t4.EffDate < isnull(t2.TermDate, '2020-12-31')
and t5.EffDate < isnull(t2.TermDate, '2020-12-31')
and t3.EffDate < isnull(t4.TermDate, '2020-12-31')
and t3.EffDate < isnull(t5.TermDate, '2020-12-31')
and t4.EffDate < isnull(t3.TermDate, '2020-12-31')
and t5.EffDate < isnull(t3.TermDate, '2020-12-31')
and t4.EffDate < isnull(t5.TermDate, '2020-12-31')
and t5.EffDate < isnull(t4.TermDate, '2020-12-31')
