Friday, October 27, 2017

FileMaker check for date range overlapping

Normally when we want to check if a datetime falls between a range of dates, we will use a SQL to count where queryDate >= startDateField and queryDate <= stopDateField.

The tricky part is to find if a range of date overlapps other range date. Common sense will say that just modify the query to

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) .

The above method works if the queryStartDate and/or queryStopDate fall within the startDateField and stopDateField. What if queryStartDate and queryStopDate is beyond the date range covered by startDateField and stopDateField? The modified query will not work as both date are outside of the date range.

To cover such condition, there is a need to check whether startDateField and/or stopDateField is in the range of queryStartDate and queryStopDate. So in addition of the modified query, we need to add extra condition like below.

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) or
(startDateField => queryStartDate and startDateField <= queryStopDate)  or
(stopDateField => queryStartDate and stopDateField <= queryStopDate)

In plain English, the query check whether the query date range overlap/within the field date range or the field date range overlap/within the query date range. Took a while for me to sort out the confusion.



No comments:

Post a Comment