The problem with a FromDate / ToDate format is that the query generates an index scan.
For example, a staff member takes vacation and returns two weeks later.
ID FromDate ToDate
15 2011-01-03 2011-01-17
Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year.
select ...
from ...
where StaffID = :Id
and FromDate >= :AsOf
and ToDate <= :AsOf;
This must examine every record of the specified staff member even though we know that the first hit found will end up being the only result. In some DBMSs, you can specify "stop at the first record found", but this is very platform dependent and not even available in all platforms. And it still might end up performing an index scan if it starts at the "wrong" end of the index. Either way, every entry must also be examined if the "as of" date is not within any range because they were not on vacation at that time.
Plus there is one slight problem. The small but still existing doubt that an empty result can mean either the staff member was not on vacation or the query failed in some way.
A method that is platform independent and can obtain the result using an index seek, is to enter a record whenever the staff's vacation status changes with only the one date of when the change took place.
For example, when first employed, a staff starts out "not on vacation" which we can designate 'O' for "on duty."
ID Status EffDate
15 O 2010-01-04
A year later he takes vacation and returns two weeks later.
ID Status EffDate
15 O 2010-01-04
15 V 2011-01-03
15 O 2011-01-17
Now you have a date and you want to know if staff #15 was on duty or on vacation at that time.
select sv1.ID, sv1.Status
from StaffVacation sv1
where sv1.StaffID = :Id
and sv1.EffDate =(
select Max( sv2.EffDate )
from StaffVacation sv2
where sv2.StaffID = sv1.StaffID
and sv2.EffDate <= :AsOf );
The subquery may look like a time-wasting complication but, assuming an index on (StaffID, EffDate), it finds the one result with an index seek. Returning that value to perform a straight comparison, the outer where
clause also performs an index seek, but it seeks to the same record found by the subquery so it really doesn't have to seek very far. That record is still in cache somewhere.
If the "as of" date is the current timestamp, the current status of the staff is shown. Omit the staff id in the where
clause and you get the current status of all staff.
There is also the (admittedly small) benefit that, as long as the date was anywhere in the employment span of the staff member, the query will always return something, either 'O' or 'V'. If the index is also defined as unique (no two dates can be the same for any staff -- an obvious requirement), it is then not possible to have gaps or overlaps between status changes.
One additional feature that may or may not count as a benefit. If the vacation times are planned in advance (as most are), the "on vacation" and "return to duty" entries may be entered in advance. As soon as the staff member's application for vacation is approved, the entries are made and no followup is needed -- as long as the vacation goes as planned, of course...