Hi,

At June 6, 2004, 11:29, Lee Jenkins wrote:

> I have a table thus:

> CREATE TABLE SCHEDULE (
> SERVERID INTEGER,
> SCHEDULESTART DATE,
> SCHEDULEEND DATE,
> SCHEDULECLOSE BLANKSMALLINT,
> SCHEDULEDATE DATE,
> SCHEDULEID INTEGER NOT NULL,
> STORENUMBER BLANKINTEGER,
> TSTAMP DEFAULTNOW,
> POSITIONID BLANKINTEGER,
> SCHEDULER_ID BLANKINTEGER,
> RATE DEFAULTNUMERIC,
> TOTALHOURS BLANKDOUBLE
> );

> In my Delphi app, I have a grid
> (http://65.39.139.136/images/scheduler.jpg)
> that shows blocks of scheduled time in a color coded manor. As of now, I
> have to do much processing on the client side to determine how to show the
> "time blocks" in the app. What I would like to do is have a selectable SP
> that I feed as parameters the starting and ending times the user would like
> to see and have that SP return the desired records.

> For instance, if there was a record:

> ScheduleStart = 10:00:00 AM
> ScheduleEnd = 4:00:00 PM

> and the user indicated 12:00:00 PM to 3:00:00 PM, the above record should be
> chosen because it falls into the range of time selected by the user.

> I hope this makes sense, but it was a late night last night and I couldn't
> sleep in today :)

Here's how I usually write my WHERE clauses for scheduling:

((ScheduleStart < UserStart) and (ScheduleEnd > UserEnd))
OR (ScheduleStart BETWEEN UserStart and UserEnd)
OR (ScheduleEnd BETWEEN UserStart and UserEnd)

Hope this helps.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)