Table 1

Table 2

I want to know which records in Table2 have Tab2_Active Date > Active Date in Table 1 and Tab2_Inactive Date < Inactive Date in Table 1. For example in this the scenario the date Tab2_Active Date 2018/05/22 mentioned in Table 2 is greater than 2018/05/20 mentioned in table 1.

So the o/p will be

Since I only have only Ids to join as the keys for 2 tables and I need to compare the dates, I cannot take dates to join the tables which results in inaccurate data.

CodePudding user response：

Create table #T1
(
Loc_Id int,
Label_Id int,
Active_Date date,
Inactive_Date date
)

Create table #T2
(
Label_Id int,
Active_Date date,
Inactive_Date date
)

Insert into #T1 
Select 1, 1001, '2022-05-13', '9999-12-31'
union
Select 2, 1001, '2022-05-20', '2022-05-12'
union
Select 3, 1001, '2022-06-14', '2018-05-12'
union
Select 4, 1001, '2022-07-14', '2018-08-13'


Insert into #T2 
Select  1001, '2022-05-13', '9999-12-31'
union
Select 1001, '2022-05-22', '2022-05-12'
union
Select 1001, '2022-06-14', '2018-05-12'
union
Select 1001, '2022-06-14', '2018-05-12'
union
Select 1001, '2022-07-14', '2018-08-12'


;with Cte as
(
Select Label_Id, Active_Date, Inactive_Date  from #T2
EXCEPT 
Select Label_Id, Active_Date, Inactive_Date from #T1
)
Select t1.Loc_Id, t2.Active_Date, t2.Inactive_Date 
from #T1 t1 
inner join Cte t2 on t1.Label_Id = t2.Label_Id and (t2.Active_Date > t1.Active_Date and t2.Inactive_Date = t1.Inactive_Date)
union 
Select t1.Loc_Id, t2.Active_Date, t2.Inactive_Date 
from #T1 t1 
inner join Cte t2 on t1.Label_Id = t2.Label_Id and (t2.Inactive_Date < t1.Inactive_Date and t2.Active_Date = t1.Active_Date)


Drop table #T1
Drop table #T2

CodePudding user response：

Here's what I came up with

with t1 as (
    select * from (values
        (1, 1001, '2022-05-13', '9999-12-31'),
        (2, 1001, '2018-05-20', '2022-05-12'),
        (3, 1001, '2012-06-14', '2018-05-12')
    ) as x(Loc_ID, Label_Id, Active_Date, Inactive_Date)
),
t2 as (
    select * from (values
        (1001, '2022-05-13', '9999-12-31'),
        (1001, '2018-05-22', '2022-05-12'),
        (1001, '2012-06-14', '2018-05-12')
    ) as x(Label_Id, Active_Date, Inactive_Date)
)
select t1.*, '||', t2.*
from t1
join t2
    on t2.Active_Date >= t1.Active_Date
    and t2.Inactive_Date <= t1.Inactive_Date
    and (
        t1.Active_Date <> t2.Active_Date 
        or t1.Inactive_Date <> t2.Inactive_Date
    )

Ignoring the CTEs (that's just a way to get the data into a tabular structure), the join criteria in the SELECT statement say that there must be partial overlap in the interval (which are the first two predicates on only one of active_date or inactive_date) but not complete overlap (which is the compound predicate saying that at least one of active_date or inactive_date must not match).