SQL Query checking on a time range

[cooling]

SELECT Facility.[Facility Name]
FROM Facility
LEFT OUTER JOIN Reservation on Facility.FacilityID = [Reservation].FacilityID
AND [Check-in time] = '" & Integer.Parse(cboHour.Text) & "' 
AND [Check-in date] = '" & lstDate.SelectedItem.ToString & "' 
WHERE Reservation].ReservationID Is NULL
      AND Facility.[Facility Type] = '" & cboFacilityType.Text & "'

I have this sql statement which only return me the facilities that are available. The problem is, I wanted it to check for the check-out time as well. Eg, A user make a reservation on 3/11/2013 10am and the duration is 2 hours. When the another user make a reservation on 11am on the same date, this facility will not be displayed for him to choose. Anyone have any idea? Thanks.

This is the sample data for Reservation and facility table.

    ReservationID MemberID FacilityID Check-in date Check-in time Check-out time CheckedIn Duration
    1              M00001        1      2013-03-12    10:00:00     12:00:00          1        2
    2              M00002        3      2013-03-12    12:00:00     14:00:00          1        2

Facility table,

FacilityID  Facility Type     Facility Name     Availability
1              Badminton           Court 1         1
2              Badminton           Court 2         1

Im gonna pass in the time for cboHour.text, example, 10am will be 10:00:00 lstDate will be 2013-03-11

[IIM]

you can convert date to seconds and add time by converting it into seconds.Similarly for checkout and then just check if current time doesnot lie between the two times in seconds.

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_from-unixtime

[AleMonteiro]

I'm not sure this will work, but I'd try something like it:

SELECT 
    Facility.[Facility Name]
FROM 
    Facility
        LEFT OUTER JOIN Reservation 
            ON (
                    Facility.FacilityID = [Reservation].FacilityID
                AND [Check-in time] = '" & Integer.Parse(cboHour.Text) & "' 
                AND [Check-in date] = '" & lstDate.SelectedItem.ToString & "' 
            )
WHERE 
    [Reservation].ReservationID Is NULL
    AND Facility.[Facility Type] = '" & cboFacilityType.Text & "'
    AND Facility.FalicityID NOT IN (
        SELECT R.FalicityID FROM [Reservation] AS R
        WHERE 
            [Check-in date] = '" & lstDate.SelectedItem.ToString & "' 
            AND [Check-in time] BETWEEN DATEADD(hour, -R.Durantion, '" & Integer.Parse(cboHour.Text) & "' ) AND '" & Integer.Parse(cboHour.Text) & "'
    )

[cooling]

@AleMonteiro

Thanks for your reply. But i got this error.
The data types time and datetime are incompatible in the greater than or equal to operator.

[cooling]

@AleMonteiro

I found out where's the problem. But now, how can i put back the checked out facility?
Eg, Facility 1 is check in on 10am and check out on 12pm, it suppose to display facility 1 for the user to select again when it reached 12pm.

[AleMonteiro]

cooling,

is this MySQL or SQL Server?

[cooling]

SQL Server

[AleMonteiro]

Let me ask, in the actual scenario, if the user selects 13pm(and there's a reservation from 10 to 12) the facility is displayed?

[adam_k]

Read here: http://www.daniweb.com/web-development/databases/threads/422523/sql-date-question
It's similar to what you are doing, but the it deals with dates and not dates and times. The same logic should apply.
Also, is there any chance the check out time is on a different date?

[cooling]

@AleMonteiro
Yes, the faciliy will be displayed.

@adam_k
The check out time only for today's time.

I had another problem, which after the user cancelled the reservation and the facility will be released for selection again.

I have a column, CheckedIn which will store '2' if the user cancelled the reservation or by default it will store '0' when the reservation is made.

Here is my SQL from AleMonteiro

        SELECT Facility.[Facility Name] FROM Facility
        LEFT OUTER JOIN Reservation ON (Facility.FacilityID = [Reservation].FacilityID
        AND [Check-in time] = '" & cboHour.Text & "' 
        AND [Check-in date] = '" & lstDate.SelectedItem.ToString & "') 
        WHERE [Reservation].ReservationID Is NULL
        AND Facility.[Facility Type] = '" & lstType.SelectedItem.ToString & "'
        AND Facility.FacilityID NOT IN (  SELECT R.FacilityID FROM [Reservation] AS R WHERE 
         R.[Check-in date] = '" & lstDate.SelectedItem.ToString & "' 
         AND R.[Check-in time] BETWEEN DATEADD(hour, -R.Duration, '" & cboHour.Text & "' ) AND '" & cboHour.Text & "')

[AleMonteiro]

If my logic is correct, just update this line:

AND R.[Check-in time] BETWEEN DATEADD(hour, -R.Duration, '" & cboHour.Text & "' ) AND '" & (int.Parse(cboHour.Text) - 1) & "')

[adam_k]

Why would you join and then include the same table in a (NOT) IN statement ? This is just a huge waste of resources.

    SELECT Facility.[Facility Name] FROM Facility
    LEFT OUTER JOIN Reservation ON (Facility.FacilityID = [Reservation].FacilityID
    AND [Check-in time] = '" & cboHour.Text & "' 
    AND [Check-in date] = '" & lstDate.SelectedItem.ToString & "') 
    WHERE ([Reservation].ReservationID Is NULL or [Reservation].CheckedIn = 2 )
    AND Facility.[Facility Type] = '" & lstType.SelectedItem.ToString & "'
    AND [Reservation].[Check-in time] BETWEEN DATEADD(hour, [Reservation].Duration, '" & cboHour.Text & "' ) AND '" & cboHour.Text & "')

If the rest of the query works, this should cover your cancellation needs, but again I think that you are not checking or the possible variations with time. I believe it should be - like the post I've linked to - something like :

   select f.[Facility Name] from Facility f 
    left join 
        (Select FacilityId, CheckedIn
        from Reservation 
        where [Check-In Date] = @CheckInDate
        and ([Check-in Time] <= @CheckInTime and [Check-out Time] >= @CheckInTime
        or [Check-In Time] <= @CheckOutTime and [Check-out Time] >= @CheckOutTime 
        or [Check-In Time] <= @CheckInTime and [Check-out Time] >= @CheckOutTime 
        or [Check-In Time] between @CheckInTime and @CheckOutTime
        or [Check-out Time] between @CheckInTime and @CheckOutTime)
    ) R 
    on f.FacilityId = r.FacilityID
    where (r.ReservationID IS Null or R.CheckedIn = 2 ) 
    and f.[Facility Type] = @FacilityType 

What this second query should do is check if there is a reservation in the specific date, where it starts before your desired checkin time and finishes during your desired period
or starts after your desired period has started and finishes after your period has ended
or starts before your period and ends after it
or is during your period. I'm using variables to keep everything simple, and I'm using a derived table instead of the actual table, in order to reduce the number of records returned and the resources used.

Please note that I haven't tested any of these 2, so they might contain errors.

[cooling]

Hi adam_k,

I had tried the first query and it return no result to me.

Edit: After I modified the last line of the query to
" AND [Reservation].[Check-in time] BETWEEN DATEADD(hour, -[Reservation].Duration, '" & cboHour.Text & "') AND '" & cboHour.Text & "' "
It gave me the cancelled reservation only.

For the second query, is it I have to pass in the value via parameters?

[AleMonteiro]

cooling,

did you try using the last line as?

AND R.[Check-in time] BETWEEN DATEADD(hour, -R.Duration, '" & cboHour.Text & "' ) AND '" & (int.Parse(cboHour.Text) - 1) & "')

[cooling]

@AleMonteiro

Yes, I tried. But the (int.Parse(cboHour.Text) gave me 'input string was not in a correct format'.
my combobox value is like 10:00:00

[AleMonteiro]

Oh, ok, so you'll have to use like this:

AND R.[Check-in time] BETWEEN DATEADD(hour, -R.Duration, '" & cboHour.Text & "' ) AND DateAdd(hour, -1, '" & cboHour.Text & "')

[adam_k]

For the second query, is it I have to pass in the value via parameters?

No, but is makes it easier to keep track of things when you use parameters, so I use them to give you an understanding of what's going on.