Inbetween Start and Enddate

[Lotek02]

I have a Database in which we have a Start and a End Date the problem is that we need to search for people who may be off between those dates. for Exp.

If John had July 1 threw the 15th off and we did a search for every one who was off on July 12th, we don't see John because the only date we get are July 1st and 15th for John.

Any Ideas.
Thank you.

 

[MakeItSo]

You could add a field to your table containing the status of each person. Then you could set this status to &quot;OnVacation&quot; or so, If StartDate of vacation <= Date() and EndDate >= Date().

Then you can filter for this status.

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Golom]

This is a little complicated but you need to test for three different conditions.

* John leaves between the two dates.
* John returns between the two dates.
* John leaves before the start and returns after the end date.

You probably need something like:

Select ... From ...
WHERE    (StartDate <= LeaveDate AND EndDate > LeaveDate)
      OR (StartDate <  ReturnDate AND EndDate <= ReturnDate)
      OR (StartDate >  LeaveDate AND EndDate < ReturnDate)