Search Between Dates

[Redcarl]

This is confusing so I will do my best to explain.

We have an app where employees sign out of the office. They supply there userID, LeaveDate, and ReturnDate and it is written into one record in SQL.

There are also several searches for this data, one of which is to enter two dates and a userID and return whether that user is signed out between those two dates by looking at whether the LeaveDate is between the two search dates.

The problem arose that if someone was signed out from the 8th to the 15th, and a search was done on them for the 11th to the 13th, it would not return anything since the LeaveDate (8th) did not fall between the two search dates.

So now I am tasked with a way to fix this. Is there a search between two dates and determine if any date in that span is in the span of dates that the employee is signed out? Is it possible to, when the employee signs out, create a record for each date in the span of days they are gone? So if someone signed out from the 10th to the 12th, it would create three different entries.

Any help is appreciated.

[Dan_Bracuk]

<cfscript>
Dates=ArrayNew(1);
ThisDate = TheFirstDate;
ctr = 1;
while ThisDate lte TheLastDate {
Dates[ctr] = ThisDate;
ThisDate = DateAdd("d", 1, ThisDate);
ctr = ctr +1;
}
NumberOfDates = ArrayLen(Dates);
<cfscript>

<cfquery>
insert into yourtable
(employee_id, DateAway)
<cfloop from = "1" to = "#NumberOfDates#" index = "ii">
select distinct #employee_id#, #Dates[ii]
from SomeSmallTable
<cfif ii lt NumberOfDates>
union
</cfloop>
</cfquery>

[cf_dev2 · Accepted solution]

> return whether that user is signed out between those two
> dates by looking at whether the LeaveDate is between the
> two search dates

Start by creating a few date examples that will cover all possibilities. Such as

If the search dates are:

Search Start = 11th
Search End = 13th

You want to include these cases

1. Date1 is before Search Start ..AND.. Date2 is between Search Start and End
Example: 6th to 13th

2. Date1 is between Search Start and End ..AND...Date2 is after search End
Example: 9th to 17th

3. Date1 is between Search Start and End ..AND...Date2 is between Search Start and End
Example: 7th to 12th

4. Date1 is before Search Start ...AND...Date2 is after Search End
Example: 5th to 21st

Next translate those conditions to sql and test with some sample values. If your fields contain dates only the comparisons are simple. If they contain a date and time be sure to properly account for the time in your comparisons.

There are better ways to write this but here is an example of how you might start translating the 4 conditions above. Once you've got the initial query working you can improve the sql.

Psuedo-CF/SQL

WHERE ( Date1 < #Start# AND Date2 BETWEEN #Start# AND #End# )
OR ( Date1 BETWEEN #Start# AND #End# AND Date2 > #End# )
OR ( Date1 BETWEEN #Start# AND #End# AND Date2 BETWEEN #Start# AND #End# )
OR ( Date1 < #Start# AND Date2 > #End#)

[cf_dev2]

If you don't need to do anything more advanced like displaying the start/end sign out dates, then the previous suggestion may be a better option.