Find if one range of dates overlaps another range of dates

[DrSmyth]

Hi,

I'm making a holiday database and have to add a query to find out if one set of dates (being booked) overlap another set of dates (Already booked).

My minds gone blank as to how to do this...

Can anybody help?

 

[MichaelRed]

Public Function basOvrLap(StrtDtA As Date, EndDtA As Date, StrtDtB As Date, EndDtB As Date) As Boolean

    'Purpose determie if two date range (intervals) overlap
    'Michael Red    9/28/04

    If (StrtDtB >= StrtDtA And StrtDtB <= EndDtA) Then
        basOvrLap = True
    End If

    If (EndDtB >= StrtDtA And EndDtB <= EndDtA) Then
        basOvrLap = True
    End If

End Function

but please add some rudimentart error checking before actual use.




MichaelRed

 

[DrSmyth]

Cheers Michael, I'll give it a go.....

 

[DrSmyth]

Michael,

THE SET OF DATES I NEED TO CHECK AGAINST ARE IN A TABLE AND THERE ARE MANY OF THEM, WOULD THIS FUNCTION STILL BE SUITABLE FOR THAT PURPOSE OR WOULD I HAVE TO ADAPT IT IN SOME WAY?

(SORRY PROBABLY SHOULD HAVE MENTIONED THIS BEFORE)

P.S. EXCUSE THE CAPITALS, I TEND TO LOOK AT THE KEYBOARD WHEN I'M TYPING AND DIDN'T SPOT IT... I ALWAYS THINK THAT OVERUSE OF CAPITALS LOOKS LIKE SHOUTING!!! SO APOLOGIES FOR ANY OFFENCVE CAUSED

 

[JonFer]

Use this Where clause in a query to find overlaps. You can change the >= to > if endpoint overlap is okay (like checkin/checkout dates).

Where a.NewStart <= b.CurrentEnd and a.NewEnd >= b.CurrentStart

John

http://www.interautomateconsulting.com

 

[MichaelRed]

JonFer

It looks like it is incomplete, addressing encaptulation, not simple overlap.

DrSmyth
Yes

Something like ~
OvreLap: basOvrLap(StrtDtA, EndDtA, StrtDtB, EndDtB)

Should work for individual records. To compare values from different records would require either a self join or a subquery and -depending on more specific details of the structure and requirements- some more elaboprate code.



MichaelRed

 

[PHV]

A pure SQL suggestion to find overlaps:
WHERE ((aStart Between bStart And bEnd) OR (aEnd Between bStart And bEnd))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Michael - Nope, it's complete. My solution find all overlaps. I think you are reading "a.NewStart < b.CurrentStart" which isn't what I posted.

PHV - If aStart is before bStart and aEnd is after bEnd then the record won't meet the WHERE criteria but is an overlap.


John

http://www.interautomateconsulting.com

 

[PHV]

Oops, you're right John.

 

[DrSmyth]

Thanks for all your help guys, I ended up using Jonfer's solution and it was pretty simple to incorporate...

Dr