SQL Help - Duplicate IDs

[SantasLittleHelper]

I have the 2 tables below:
Table1

ID	Start Date	End Date
ABC123	01/04/2020	13/09/2020
DEF456	03/01/2020	17/02/2020
ABC123	15/09/2020	30/09/2020

Table2

ID	Start Date	End Date
ABC123	17/12/2019	12/07/2020
ABC123	13/07/2020	11/11/2020
DEF456	07/07/2020	09/09/2020
DEF456	24/09/2020	31/10/2020

I want to create Table 3 below based on this criteria.

In text, I want to add a True/False flag column to show if a Client ID had an open record between the two tables at the same time. The difficulty is that there can be multiple duplicate IDs in each table

Is anybody able to help with the SQL?

IF 
THERE IS A ROW
WHERE( 
AND(
  (Table1.ID = Table2.ID),
  (Table1.[End date] >= Table2.[Start Date]),
  (Table1.[Start date] <=  Table2.[End Date]))
1,0)

The Final table should look like this:

Table3

ID	Start Date	End Date	Supported During
ABC123	17/12/2019	12/07/2020	1
ABC123	13/07/2020	11/11/2020	1
DEF456	07/07/2020	09/09/2020	0
DEF456	24/09/2020	31/10/2020	0

 

[xenou]

Your description says "between the two tables" but the Final table is listing only records from table 1. Why is that? Not sure what this means. You can say A overlaps B, but you can also say B overlaps A. What is driving the choice on this? "Supported During" isn't clear to me as a result.

 

[SantasLittleHelper]

For context, Table 1 relates to External Support for each Client ID. Table 2 relates to Internal Support for each Client ID.

We want to create a column on Table 2 which flags which Internal Periods of Support (Rows in Table 2) involved the same Client also receiving External Support (Rows in Table 1).

Does this make more sense?

 

[xenou]

I think I will have time to look into this a bit later today ... haven't forgotten about it though. I don't want to give any rushed answer as I'm not well-versed in comparing date ranges and I'd like to consider carefully as I explore the possible solutions.

 

[xenou]

I apologize in advance that I don't have Microsoft Access on my machine at work, so I will have to post a solution in TSQL. However, I think you can make the necessary adjustments easily enough. The most obvious difference is that TSQL has a case statement where MSAccess will require an IIf(IsNull()) structure, or simply an Nz() to check for nulls.

The basic strategy of the solution is:

• in the inner result set we get the cases where there is overlap in support
• in the outer query we are just repeating all the rows from the internal query, but flagging the ones that have a match in the inner query (i.e., have an overlap in support)

First of all, my test data:

drop table if exists #InternalSupport
create table #InternalSupport (
    ID varchar(10),
    StartDate datetime,
    EndDate datetime
)

drop table  if exists #ExternalSupport
create table #ExternalSupport (
    ID varchar(10),
    StartDate datetime,
    EndDate datetime
)

insert into #InternalSupport (ID, StartDate, EndDate)
    values
     ('ABC123','12/17/2019','7/12/2020')
    ,('ABC123','07/13/2020','11/11/2020')
    ,('DEF456','7/7/2020','9/9/2020')
    ,('DEF456','09/24/2020','10/31/2020')
    
insert into #ExternalSupport (ID, StartDate, EndDate)
    values
     ('ABC123','4/1/2020','09/13/2020')
    ,('DEF456','1/3/2020','2/17/2020')
    ,('ABC123','09/15/2020','9/30/2020')

Then the result:

select i.*, case when i2.ID is null then 0 else 1 end as SupportedDuring
from 
    #InternalSupport i
    left join 
    (
    select distinct i.* from #InternalSupport i
    inner join #ExternalSupport e
    on i.ID = e.ID
    where 
        (
            (i.EndDate >= e.StartDate and i.StartDate <= e.StartDate)
            or (i.StartDate <= e.EndDate and i.StartDate >= e.EndDate)
        )
    ) i2
on i.ID = i2.ID
and i.StartDate = i2.StartDate
and i.EndDate = i2.EndDate