Finding overlapping dates

[wynstonh]

Afternoon all.

I am trying to solve a bit of a problem and hoping someone here may have some advice for me.

I have one table of data (contracts) and need to extract from this any instances where the dates overlap.

So the logic should be:

ref <> ref ok
ref = ref and Start Date > End Date ok
ref = ref and Start Date <= End Date overlap
ref = ref and End Date < Start Date ok
ref = ref and End Date >= Start date overlap

I've tried http://allenbrowne.com/appevent.html but doesn't seem to work with my data potentially because of trying to exclude instances where the ref does not match.

What I have in the field of the query designer is:

NoClash: ([tbldata_1].[S Date]>[tbldata].[E Date]) Or ([tbldata_1].[E Date]<[tbldata].[S Date])

But what I'm getting is multiple entries per ref # and both overlapping and not overlapping date ranges.

Any advice would be much appreciated.

 

[jdraw]

See this article from pBaldy.

 

[Ranman256]

Make 2 queries, 1 for each overlap condition you showed.
Make a 3rd Union query that contains the 2 queries above. Run the union query.

Select * from qsOverlapStart
Union
Select * from qsOverlapEnd

 

[CJ_London]

Baldy web has the solution but think your logic is wrong

you say
ref <> ref ok
ref = ref and Start Date > End Date ok
ref = ref and Start Date <= End Date overlap
ref = ref and End Date < Start Date ok
ref = ref and End Date >= Start date overlap

If OK means it does not overlap then I would have thought you want

ref = ref ok (it's the same record)
ref <> ref and Start Date > End Date ok
ref <> ref and Start Date <= End Date overlap
ref <> ref and End Date < Start Date ok
ref <> ref and End Date >= Start date overlap

and this
ref <> ref and Start Date <= End Date overlap

I would say is only an overlap if End Date is also <Start Date

 

[wynstonh]

Good morning & thanks for your replies.

@jdraw - pbaldy's solution doesn't quite match my need as a) it requires the date range to be entered in a form and I don't really want the user to have to do that. I want the entire table to be checked against itself whenever new data is added (via an import from excel) and b) it doesn't cover the exclusion of records where the ref does not match

@RanMan256 - I've never used union queries. I'll have a play around with it this morning but still not sure how I exclude records where one field (ref) is not matching.

@CJLondon - think of ref like a customer. I only want to find instances where the same customer has overlapping contracts. So I want to exclude instances of overlapping where the customer is different but not where the customer is the same.
So customer A, contract 1 start date 01.01.17 and end date 31.12.2017 overlaps customer A, contract 2 start date 01.11.2017 and end date 31.10.2018.
You mght be right about ref = ref and Start Date <= End Date overlap though, that looks wrong to me with a fresher head.
pbaldy's solution doesn't quite fit though for the reasons above

 

[CJ_London]

paul solution is easily adapted

take your point about what you are trying to find - so query would be

SELECT DISTINCT A.*
FROM myTable A INNER JOIN myTable B ON A.ref=B.ref
WHERE a.start<=b.end and a.end>=b.start

 

[wynstonh]

Thanks. Looks good, just what I need but one tweak may be required that I can't seem to get my head around.

My code is:

SELECT DISTINCT A.*
FROM tbldata AS A INNER JOIN tbldata AS B ON A.[MPAN] = B.[MPAN]
WHERE (((A.[Project ID])<>.[Project ID]) AND ((A.[S Date])<=.[E Date]) AND ((A.[E Date])>=.[S Date]));

which is returning all the overlaps eg:

MPAN A Project 1 S Date 01.10.2015 E Date 30.09.2017 overlaps MPAN A Project 2 S Date 01.10.2016 E Date 30.09.2017

but is also returning some where there is no overlap eg:

MPAN B Project 3 S Date 01.03.2015 E Date 29.02.2016 does not overlap MPAN B Project 4 S Date 01.03.2016 E Date 30.04.2017

Might just be me being daft this morning but how do I also exclude the 2nd example.

 

[CJ_London]

not sure, but it might have something to do with date interpretation between UK and US formats

29.02.2016 will be returned as 29th Jan because there are only 12 months in the year - it is also a leap year so this may impact on it's evaluation

given the UK format, you would expect 01.03.2016 to be interpreted as 1st March - but in US it would be interpreted as 3rd Jan - which is less than 29th Feb

Dates are stored as numbers, right now it is 42748.4292476852
42748 is the date - 42749 will be the value for tomorrow. The decimal part is the time now based on the number of seconds so far today divided by 86400 (the number of seconds in a day).

It is possible that somehow both 29/2 and 1/3 are being treated as equal in some way because of the leap year and therefore returns true. you may have discovered an Access bug!

First I would inspect your data to see if the leap year theory holds up. If it does you'll need a fix. Inspect the actual values of the date fields (in a query use something like ASUnderlyingValue: cdbl(A.[s date]) and see if you can see something there, otherwise you might need an iif or dateadd function to tweak values slightly

 

[wynstonh]

looked at the data & it's definitely not the date format - the absolute values are what I'd expect them to be but still causing more data returned than I need.

I've tried adding another condition to the WHERE statement but it just returns the same data so I'm a bit stuck again.

Where would I put the iif?

 

[CJ_London]

what about the leap year issue? - the solution required depends on whether or not you are getting round an access 'bug'

the absolute values are what I'd expect them to be

what does this mean - you are seeing 42429 for 29th Feb and 42430 for 1st March?

you could try changing

A.[S Date])<=.[E Date])

to

clng(A.[S Date])<=clng(.[E Date])

 

[wynstonh]

Great!!

got it to work with a bit of tinkering around the dates.

Thanks so much for your assistance

 

[CJ_London]

Happy to help. For the benefit of others following this thread, please can you post your final solution.