Checking if a date in a period matches to another period.

[telmessos]

Hi,

I would like to explain my problem with an example. I have a form where clients will enter their reservation beginning and end date. Let's say the beginning date is 20/08/2006 and end date is 27/08/2006. I would like to connect the database for that property and check if any days between these dates are booked to stop double reservation.
My database fields are ID, PropID, StartDate, EndDate

Any idea? Thanks

Ceyhun

[russell]

what db are u using?

[telmessos]

I use MySQL

[russell]

Your query should look something like this:

select ID
from yourTable
where PropID = xxx
And StartDate Between yyy And zzz
And EndDate Between yyy And zzz

If your query returns anything, you have an overlap -- or double booking.

You may be able to simplify things by storing every date in your db (rather than just start/end ) like a calendar.

Also, may want to have a look at the MySQL Reference (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html) for comparison operators.

[telmessos]

Hi russell,

This SQL you have written looked like checks only if the startDate and EndDate is between those dates. I have to check if each single day between the chosen dates is between StartDate and EndDate records we get from the database.

Let's say we request date1 and date2 from a form. We connect to the database and get the records for the given PropID. We need to check if the "period between date1 and date2" is between "StartDate and EndDate records" we get from the database. Because even one day which is in the date1-date2 period matches a day in one of the StartDate-End Date period days, code must give a warning.

Thanks

[silverbullet24]

you could do something like

select ID
from yourTable
where PropID = xxx
And (StartDate >= yyy And StartDate <= zzz)
And (EndDate >= yyy And EndDate <= zzz)

at least, i think so. i've done something just like this but i don't have the code handy to check it out at the moment. this is the idea i used though

[telmessos]

I think one SQL command is not enough for that. I think I need to split the days between date1 and date2 and check if it's between the dates in the database with the help of a loop. But I don't know how to do that.

[silverbullet24]

ok i looked at my code that i have, would this work?

SELECT ID FROM table WHERE ((StartDate >= 'yyy' AND StartDate < 'zzz') OR (EndDate > 'yyy' AND EndDate < 'zzz') OR (StartDate <= 'yyy' AND EndDate >= 'zzz')) AND PropID = xxx

yyy = start date
zzz = end date

i don't think its quite right yet, but i think you should be able to do it without a loop

[russell]

something like this will work. you may need to make some modifications. basically, it loops from startDate to endDate checking if any date in between is already reserved.

<%
Dim arReservedDates '' store reserved dates for this propid
Dim startDate, endDate

startDate = cdate(Request.Form("startDate"))
endDate = cdate(Request.Form("endDate"))

arReservedDates = getReservedDates(propId, startDate, endDate)

checkDates startDate, endDate

Sub checkDates(dt1, dt2)
Dim dt
Dim blnOverlap

'' loop through every date between startDate (dt1) and endDate (dt2)
'' and check if they are already reserved...
For i = 0 to Abs(DateDiff("d", dt1, dt2))
dt = cdate(dt1) + i

If Not isDateAvailable(dt) Then
Response.Write dt & " already booked<br>" & vbCrLf
blnOverlap = True
End If
Next

If blnOverlap Then
Response.Write "Unable to create this reservation"
Else
Response.Write "Ok to create this reservation"
End If
End Sub

Function isDateAvailable(dt)
'' check to see if date is reserved already
Dim i
Dim checkDate

If not isArray(arReservedDates) Then
isDateAvailable = True
Exit Function
End If

If isEmpty(arReservedDates) Then
isDateAvailable = True
Exit Function
End If

For i = 0 to Abs(DateDiff("d", cdate(arReservedDates(0, 0)), cdate(arReservedDates(1, 0))))
checkDate = cdate(arReservedDates(0, 0)) + i
If cdate(dt) = cdate(checkDate) Then
isDateAvailable = False
Exit Function
End If
Next

isDateAvailable = True
End Function

Function getReservedDates(propId, startDate, endDate)
Dim sql
Dim cmd
Dim rs

sql = "SELECT min(startDate), max(endDate) " &_
& "FROM reservations " &_
& "WHERE propid = " & propId & " " &_
& "And (startDate between '" & startDate & "' And '" & endDate & "' " &_
& "Or endDate between '" & startDate & "' And '" & endDate & "')"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = YOUR_CONNECTION_STRING
.CommandType = 1
.CommandText = sql

rs.Open .Execute
End With

If not rs.EOF Then
getReservedDates = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>

[buster777]

I have got the same problem but within MS Access. Is there any easy fix to dit it here?

[telmessos]

I sorted it out with the following way checking it day by day with a loop

date1 = Request.Form("perStartDate")
date2 = Request.Form("perEndDate")
For i = date1 to date2
SQL="Select * from rentacardonemler where ('"&i&"' between baslangic and bitis)"
Set ors=DbObj.Execute(SQL)
if not ors.EOF then
booked = "1"
end if
Next
if not booked = "" then
Response.Write "This means all or some days in that period matches the dates in the database"
else
your code...

Hope it helps

Thanx
Ceyhun