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

What should be selected if StartDate >= 28/09/2007 06:00:00?

If both, than you can change your AND to OR and it will work...

|||

This is a logic issue:

There are several logical possibilities. First, let's define some terms that will be unambiguous in meaning :

SearchStateDate and SearchEndDate = the range of time our search includes.

UsageStartDate and UsageEndDate = the range of time a room is used.

Now, one way to solve this would be to find all the records in which the search and usage dates overlap. That's hard and it hurts my head.

Or, we could try from the opposite direction, by removing all the ones that do not overlap, and keeping the rest.

Sometimes it's easier one way, sometimes it's easier the other. :)

This query returns all the rows thatDO NOT overlap:

select RoomId from RoomSchedule as usage

where usage.EndDate < @.SearchStartDate -- finished using the room before our search starts

or usage.StartDate > @.Search.EndDate -- started using the room after our search ends

Which means that this query returns all the rows thatDO overlap:

select * from RoomSchedule as overlaps

where overlaps.RoomId not in

(

select RoomId from RoomSchedule as usage

where usage.EndDate < @.SearchStartDate

or usage.StartDate > @.Search.EndDate

)

If we were using Oracle, it would be even easier, because Oracle implements a minus operator, which is like a union operator in reverse.

select * from RoomSchedule as overlaps

minus

select RoomId from RoomSchedule as usage

where usage.EndDate < @.SearchStartDate

or usage.StartDate > @.Search.EndDate

The key to using sql effectively is not to think in terms of records, but to think in terms of sets.

If you can define the problem in terms of easily determined sets of data, the sql is often very simple.

|||

DECLARE @.StartDatedatetime DECLARE @.EndDatedatetime SET @.StartDate ='2007-09-28 07:30:00.000'SET @.EndDate ='2007-09-28 13:00:00.000'SELECT RoomID, StartDate, EndDateFROM yourTable

WHERE StartDate>=CONVERT(Datetime,CONVERT(nvarchar(10),@.StartDate,121) +' ' +CASEWHENDatepart(hour,@.StartDate)<=9ANDDatepart(hour,@.StartDate)>=7THEN'07:00:00.000'ELSERIGHT(Convert(nvarchar(22),@.StartDate ,126),8)END, 121)AND EndDate <=Convert(nvarchar(22),@.EndDate ,126)

|||

limno:

DECLARE @.StartDatedatetime
DECLARE @.EndDatedatetime
SET @.StartDate ='2007-09-28 07:30:00.000'
SET @.EndDate ='2007-09-28 13:00:00.000'
SELECT RoomID, StartDate, EndDate
FROM yourTable

WHERE StartDate>=CONVERT(Datetime,CONVERT(nvarchar(10),@.StartDate,121) +' ' +
CASEWHENDatepart(hour,@.StartDate)<=9ANDDatepart(hour,@.StartDate)>=7
THEN'07:00:00.000'
ELSERIGHT(Convert(nvarchar(22),@.StartDate ,126),8)END, 121)
AND EndDate <=Convert(nvarchar(22),@.EndDate ,126)

I do not think this is a workable solution for all data:

Here are the logical possibiliites:

SS = Search Start, SE = Search End.

RS = room start, RE = room end.

Timeline

============================================

SS============SE

RS=1=RE RS=2=RE

RS=3=RE

RS==============4============RE

RS===5===RE RS===6===RE

There are 6 logical possibilities in the timeline above (with >= and <=, more would be needed if > and < were appropriate to use instead ).

My suggested solution removes sets 1 and 2, leaving the other 4 sets (which are overlaps).

Your solution doesn't seem to identify all four of sets 3,4,5 and 6, unless I've misread it.

Plus, the hard-wiring of 7am into it makes me nervous. Seems like a bad approach that would not work with different data with different time ranges..

|||

Hi David,

You are right. The code I posted will not work as a whole solution. Instead, it should be treated as the way how to handle the example OP descibed in his question. He can use this logic to handle all cases he wants to.

By no means, this is the good way to do this but just one way I did. A lot of times, I focus on the approach for dealing with questions like this and hope I can share some of my thoughts with the OP.

Thanks for your input.

|||

No problem! Today is a "slow brain day" for me, it was very possible that I just didn't get it! So, it was best to ask. :)

|||

Thankyou David & Limno for your guidance and subsequent discussion.

I am really struggling to get the logic sorted in my thoughts.

I will try as suggested.

Thanks

Peter

|||

Did the query I suggested return the correct results for you?|||

The solution was to include two EXCEPT clause in my query to exclude the 2 logical problems.

"WHERE (Appointment.Start BETWEEN @.Start AND @.StartEnd) OR "

"(Appointment.StartEnd BETWEEN @.Start AND @.StartEnd) "

"WHERE (@.Start BETWEEN Appointment.Start AND Appointment.StartEnd) OR "

"(@.StartEnd BETWEEN Appointment.Start AND Appointment.StartEnd) "

Regards

Peter