Query Design / Capability

Hello, I am trying to achieve a query results with not much luck. I am struggling the query design of how to construct to get the right results.

My table has the following data

RoomID  Subject         StartDate                Duration(min)           EndDate

  201       test       28/09/2007 07:00:00            180              28/09/2007 10:00:00

  202       test       28/09/2007 09:00:00            240              28/09/2007 13:00:00

 

The best way to describe is a scheduling system with the StartDate, Duration, EndDate.

I am trying to query for datetime periods which are allocated, eg the table records show from 7am - 10am and 9am - 1pm are allocated.

When I query on StartDate >=  28/09/2007 07:00:00   AND    EndDate <= 28/09/2007 13:00:00, record 201 & 202 shows which is correct because they are within query parameters and are allocated.

When I query on StartDate >=  28/09/2007 07:30:00   AND    EndDate <= 28/09/2007 13:00:00, record 202 only shows which is correct because record 201 StartDate is before 07:00:00.

But my problem is I require record 201 to show because the time period between 07:00:00 and 09:00:00 is allocated and the query parameter is 07:30:00 which is still between 07:00:00 and 09:00:00.

I have researched where on similar situations, the use of duration is used, but I am unsure on its application to achieve results.

I have also read of other situations where a lookup table should be used, but again I cannot get my head around the application of alookup table to query the resuults.

 

Does anyone have any theories, design thoughts or real work solutions they can help me with.

 

Thanks

Peter Smith