Find Overlapps in dates (1 Viewer)

[soldat452002]

Hello

So currently im working in migrating data from two Regions (East, West) same data fields but the date ranges (Eff, Exp) either could overlap or not. Im trying to build a query to identify overlapping dates and insert them in order (Oldest to Newest date). Any help would be appreciated it.

The desired result would be to Insert a record with oldest date prior to the next record with the newest date.

My current query.

• [East].[Eff] >= [West].[Exp]
• [East].[Exp] <= [West].[eff]
• [East].[Affiliation_ID] = [West].[affiliation_ID]
• [East].[ID] = [West].[ID]

Id	West.Eff	West.Exp	West.Code	West.Affiliation_ID	East.Eff	East.Exp	East.Code	East.Affiliation_ID
19267717	3/4/2020	1/1/2200	TMUTUAL	2971515	10/17/2019	3/3/2020	COMPLETE	2971515
19267717	3/4/2020	1/1/2200	TMUTUAL	2971515	3/4/2020	1/1/2200	TMUTUAL	2971515
19313290	12/6/2019	1/1/2200	COMPLETE	2971515	11/27/2019	1/12/2020	COMPLETE	2971515
19313290	12/6/2019	1/1/2200	COMPLETE	2971515	1/13/2020	1/1/2200	TMUTUAL	2971515

 

[jdraw]

crossposted https://www.accessforums.net/search.php?searchid=363276

 

[MajP]

I do not understand what you want to do, it is not very clear. However there are four cases for overlapping that normally need to check

|---------------|A
      |--------------|B
Start of A <= Start of B and End of A <= End of B   

            |---------------|A
      |--------------|B
Start of A >= Start of B and End of A >= End of B  

     |-------|A
  |--------------|B
Start of A >= Start of B and End of A <= End of B  


 |--------------------|A
   |--------------|B
Start of A <= Start of B and End of A >= End of B

 

[plog]

First and foremost:

The desired result would be to Insert a record with oldest date prior to the next record with the newest date.

There is no order in a table. There is no first record, no last, no next, no 217th record. Order exists on data only in queries when you explicitly tell the query how to order the data.

To help you we need a bigger view of the issue. I suggest you post 2 sets of data:

A. Sample data from all tables. Include table and field names and enough data to cover all cases. Do not include any extranoeus fields. Just the fields we need to use or show in the end.

B. Expected results of A. Show us what data you expect to end up with after this query runs.

Again, 2 sets of data--what you are starting with and what you hope to end with.

 

[Bullschmidt]

It looks like West.Eff and West.Exp column data is switched. This assumes that West.Exp is supposed to be the newer date like East.Exp is to East.Eff.