Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
goong
Starting Member
2 Posts |
 Posted - 2007-10-06 : 18:21:42
|
| 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 dataRoomID 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. ThanksPeter Smith |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-10-08 : 01:37:12
|
| Something like WHERE'2007/09/28 07:30:00' BETWEEN StartDate AND EndDateOR'2007/09/28 13:00:00' BETWEEN StartDate AND EndDate? |
 |
|
|
goong
Starting Member
2 Posts |
Posted - 2007-10-09 : 21:14:55
|
| Thanks Koji,works great.regardsPeter |
|
|
|
|
|
|
Query logic problem