SQL Query checking on a time range

Question

cooling 0 Newbie Poster

11 Years Ago

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

mssql sql

Edited 11 Years Ago by pritaeas because: Moved.

4 Contributors
16 Replies
1K Views
1 Week Discussion Span
Latest Post 11 Years Ago Latest Post by adam_k

IIM 163 Master Poster

11 Years Ago

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 238 Can I pick my title?

11 Years Ago

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) & "'
    )

AleMonteiro 238 Can I pick my title?

11 Years Ago

cooling,

is this MySQL or SQL Server?

AleMonteiro 238 Can I pick my title?

11 Years Ago

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?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cooling 0 Newbie Poster · Answer 1 · 2013-03-13T05:12:58+00:00

@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 0 Newbie Poster · Answer 2 · 2013-03-13T05:48:44+00:00

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

cooling 0 Newbie Poster · Answer 3 · 2013-03-13T08:02:15+00:00

cooling 0 Newbie Poster

11 Years Ago

SQL Server

adam_k 239 Master Poster · Answer 4 · 2013-03-13T20:20:14+00:00

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 0 Newbie Poster · Answer 5 · 2013-03-19T14:41:25+00:00

@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 238 Can I pick my title? · Answer 6 · 2013-03-19T15:15:06+00:00

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 239 Master Poster · Answer 7 · 2013-03-19T18:41:49+00:00

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 0 Newbie Poster · Answer 8 · 2013-03-20T02:48:48+00:00

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 238 Can I pick my title? · Answer 9 · 2013-03-20T15:13:00+00:00

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 0 Newbie Poster · Answer 10 · 2013-03-20T15:19:34+00:00

@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 238 Can I pick my title? · Answer 11 · 2013-03-20T17:02:20+00:00

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 239 Master Poster · Answer 12 · 2013-03-20T22:33:21+00:00

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.