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