SQL Help - Duplicate IDs

SantasLittleHelper

Board Regular
Joined
Nov 25, 2016
Messages
77
I have the 2 tables below:
Table1
IDStart DateEnd Date
ABC12301/04/202013/09/2020
DEF45603/01/202017/02/2020
ABC12315/09/202030/09/2020

Table2
IDStart DateEnd Date
ABC12317/12/201912/07/2020
ABC12313/07/202011/11/2020
DEF45607/07/202009/09/2020
DEF45624/09/202031/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?

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
IDStart DateEnd DateSupported During
ABC12317/12/201912/07/20201
ABC12313/07/202011/11/20201
DEF45607/07/202009/09/20200
DEF45624/09/202031/10/20200
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 25, 2016
Messages
77
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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:
SQL:
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:
SQL:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,056
Messages
5,639,802
Members
417,116
Latest member
Jakeyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top