--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...>
wrote:

> 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)

Daniel,
Why make life so difficult? I assume that your UserStart/UserEnd are
all date variable. All you need to do is to do at your where clause
you put in ScheduleEnd <= UserStart and ScheduleEnd >= UserStart.
This should retrieve all the data you need.

However one more question, in your example above is your data type
for ScheduleStart/ScheduleEnd a date or Time or Timestamp? I am
actually confuse.... If according to your example above you are
better use timestamp, it should give you a more flexible search.

Peter