Hi,

At June 7, 2004, 23:00, kfkong wrote:

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

Stressful day! Now, that I've had some sleep, more alert and less
stressed.

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

You, Arno and Steffen are right. This is the best way to go. It's been
too long(over 3 years) that I've seen my code that deals with this,
and I was actually using what you, Arno and Steffen were suggesting.
I should've looked at my code first.

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