Guys

I have written a function and basically it checks to see a room is already being used between 2 dates.

I have got it to work by using just a single date, but I want it to actually look for whether there are records between the start date field and the end date field.

Here is the function...
****************************************
function check.room.available()
{
select tssma931.roomn
where tssma931.roomn = :tssma931.roomn and tssma931.stdt = :tssma931.stdt or tssma931.endt = :tssma931.stdt
selectdo
message("Room Unavailable, please choose another room")
input.again()
endselect
}
*******************************************
roomn = the room nunber
stdt = start date
endt = end date

So basically someone picks the room number and enters a start and end date. The function looks for entries where that room number is booked for a date range that conflicts with the range just entered.

I tried using ...
********************************************
where tssma931.roomn = :tssma931.roomn and tssma931.stdt in :tssma931.stdt to tssma931.endt or tssma931.endt in tssma931.stdt to :tssma931.endt
********************************************

This failed as Baan did not understand "in" or "to".

Does anyone have any ideas?

Cheers

Andy,

I think you are trying to do something like this:

where tssma931.roomn = :tssma931.roomn
and (tssma931.stdt inrange :tssma931.stdt and :tssma931.endt
or tssma931.endt inrange :tssma931.stdt and :tssma931.endt)


However, this misses the case where another event starts before the one in question starts and ends after this one ends.

So, you might try this instead:

where tssma931.roomn = :tssma931.roomn
and tssma931.stdt < :tssma931.endt
and tssma931.endt > :tssma931.stdt

You may want to try a different approach as you want to ensure that not only are the start and end dates free, but the dates between them as well.

My suggestion is as follows:

To check for all possibilities of overlapping dates (including a date range that is completely inside another), you would have to check each day in the range specified for being within a prebooked period for that room. This would require a loop to check each day, thus avoiding all overlap. It takes a bit longer to process, but should do what you are looking for. I have not checked the syntax (just a quick type-in) so you may have to make some minor syntactical modifications (I can't remember off the top of my head if you can do a date.to.num() in a "for" statement). The suggested code approach is below. I hope this helps.

---------------------------------------------------------------------------------

function check.room.available()
{
domain tclong errflag
domain tclong i

select
*
from
tssma931
where
tssma931.roomn = :roomn
selectdo
errflag = 0

for i = date.to.num(stdt) to date.to.num(endt)
if i inrange :tssma931.stdt and :tssma931.endt then
errflag = 1
break
endif
endfor
endselect

if errflag = 1 then
message("Room Unavailable, please choose another room")
input.again()
endif
}

Coding explanation:

The assumption is made that stdt, endt and roomn are the field names from the input screen.

An error flag is set up to check the error condition has been met. If so, a "break" is issued to avoid additional cpu usage (wasted time as the room is already known to be booked). The error flag is then checked to verify the room is available.

The "for" loop would process each date between (and including) the start and end dates by converting them to a number first (to allow the "for" loop to count correctly).

the way I see it there are four conditions:
1. start date falls in a booked period or
2. end date falls in a booked period or
3. the entire period is booked or
4. a booked period falls entirely in the new period

|*** for readability
l.stdt = tssma931.stdt
l.endt = tssma931.endt

|*** assuming start date <= end date
select tssma931.*
from tssma931
where (tssma931.stdt <= :l.stdt and
tssma931.endt >= :l.stdt) or |1.
(tssma931.stdt <= :l.endt and
tssma931.endt >= :l.endt) or |2.
(tssma931.stdt <= :l.stdt and
tssma931.endt >= :l.endt) or |3.
(tssma931.stdt >= :l.stdt and
tssma931.endt <= :l.endt) |4.
selectdo
|*** the room cannot be booked for this period
endselect

That should do it.

Regards,
Guido

Other than the use of the equal sign, if you check the logic, the four cases listed in the previous post can be summarized as I showed before with only two date conditions. I chose to leave out the equal signs because I don't think having one meeting start at the same time another ends should result in an error condition.

Food for thought...

Ed

Once again spot on m8!

I have used both of the examples you provided into 1 function and it works a treat.

Cheers m8y:D