Find available times during a range of time, using a history of available and unavailable times

Question

For a given span of time, say 2013-01-01 08:00 and 2013-01-02 08:00 (24 hours), over time someone may have sequentially entered records to indicate that they are available or not available. Where time spans overlap for a newly entered record, only different types (available or unavailable) are allowed. Availability records are preserved when changes occur.

How can I determine whether a given span of time is fully available?

For example, I may have the following records:

RecId | Start DateTime   | End DateTime     | Type
=====================================================
1     | 2013-01-01 08:00 | 2013-01-02 08:00 | Available
2     | 2013-01-01 08:00 | 2013-01-02 08:00 | Unavailable
3     | 2013-01-01 17:00 | 2013-01-02 08:00 | Available
4     | 2013-01-01 08:00 | 2013-01-02 17:00 | Available
5     | 2013-01-01 12:00 | 2013-01-02 14:00 | Unavailable

Given the records above, the individual is currently available on 2013-01-01 from 08:00 to 12:00, and 14:00 to 0800 the following morning.

I now want to check if the individual is available from, say, 2013-01-01 11:00 to 2013-01-01 15:00. The answer should be no, since the records indicate the individual is not available from 12:00 to 14:00 on that date.

If it helps, the application is to see whether someone is eligible to trade all or part of their scheduled shift with another employee. Multiple trades of different parts of the shift may be made with other individuals. Someone may trade their entire shift away, then work parts of that shift for other people, as long as parts don't overlap. We need to be able to check, when a new trade is scheduled, that the employees are actually free to trade for the block of time requested.

Answer 1

You can use the Time Period Library for .NET to calculate overlapping and intersecting time periods.