SQLQUERY

[i***@gmail.com]

RouteId SeatNumber DateofBooking StartPoint EndPoint
1 11 11/08/2012 1 2
1 15 11/08/2012 2 4
1 20 11/08/2012 4 8
1 40 11/08/2012 1 9

For a given routeid and date I would like to write stored procedure to get seatnumber which is already booked. The parameters are @RouteId, @DateofBooking, @StartPoint, @EndPoint.

For example if @RouteId=1 and @dateofbooking= 11/08/2012, @startpoint=2 and @endpoint=4 then SeatNumber 15 and 40 will show as booked. However SeatNumber 11and 20 will be available. This is because SeatNumber 15 is already booked for the same start and end points. Also SeatNumber 40 is booked for journey where startpoint<@startpoint and endpoint>@endpoint.

[Bob Barrows]

RouteId SeatNumber DateofBooking StartPoint EndPoint
1 11 11/08/2012 1 2
1 15 11/08/2012 2 4
1 20 11/08/2012 4 8
1 40 11/08/2012 1 9
For a given routeid and date I would like to write stored procedure
to get seatnumber which is already booked.

Really? You want it to return only seats which are _unavailable_?
I would think you would want it to return seats which are available, or a
list of all the seats with a flag indicating their availability.
But I will take you at your word.

The parameters are
@RouteId, @DateofBooking, @StartPoint, @EndPoint.
@startpoint=2 and @endpoint=4 then SeatNumber 15 and 40 will show as
booked. However SeatNumber 11and 20 will be available. This is
because SeatNumber 15 is already booked for the same start and end
points. Also SeatNumber 40 is booked for journey where

Well, you're almost there.Think of it this way:

A seat is booked if:
EndPoint > @startpoint and StartPoint < @Endpoint.

This is assuming you have logic in your application to prevent it from
passing a startpoint that is greater than or equal to the endpoint being
passed. Even if you do, you might wish to raise an error in the procedure if
such is the case.

[i***@gmail.com]

select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(startpoint <= 1) and
(endpoint >= 4)

RouteID SeatNumber DateofBooking StartPoint EndPoint
1 26 28/08/2012 1 9
1 40 28/08/2012 1 4
1 15 28/08/2012 1 2
1 16 28/08/2012 1 2
1 22 28/08/2012 4 9
1 24 28/08/2012 4 9

This query isnt returing required results. For example if I have to book seat from StartPoint 1 and endPoint 4 then the unavailable seat should be 22 and 24.However this query is returing SeatNumber 26 and 40.

[Bob Barrows]

Post by i***@gmail.com
select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(startpoint <= 1) and
(endpoint >= 4)
RouteID SeatNumber DateofBooking StartPoint EndPoint
1 26 28/08/2012 1 9
1 40 28/08/2012 1 4
1 15 28/08/2012 1 2
1 16 28/08/2012 1 2
1 22 28/08/2012 4 9
1 24 28/08/2012 4 9
This query isnt returing required results. For example if I have to
book seat from StartPoint 1 and endPoint 4 then the unavailable seat
should be 22 and 24.However this query is returing SeatNumber 26 and
40.

You didn't follow my explanation at all. If you had, your statement would
have read:

select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(endpoint > 1) and
(startpoint < 4)

This will return all seats except 22 and 24 - remember, your original
question said you wanted the query to return unavailable seats.

Why would 22 and 24 be unavailable (booked) in this example? They should
both be available for startpoint 1 and endpoint 4 shouldn't they? The
customer flying from 1 to 4 leaves the seat at 4 so the customer flying from
4 to 9 can occupy the seat ...

If you really want it to return available seats, which makes more sense from
a ticket-selling/purchasing standpoint, you need to change it to:

select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(startpoint >= 4) or
(endpoint <= 1)

If you want to return all seats, with a flag indicating availability, then
use a CASE statement:

select SeatNumber
, CASE WHEN (endpoint > 1) and (startpoint < 4)
THEN 'Available'
ELSE
'Unavailable'
END As Availabity
from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012')

[i***@gmail.com]

Thanks alot Mr Barrow. The query
select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and

(endpoint > 1) and
(startpoint < 4)
is returing correct results. It fails in this scenario
Suppose StartPoint is 4 and Endpoint is 9 then 22, 24, 26 and 40 should be unavailable. This query only returns 22, 24 and 26 in that case.

[Bob Barrows]

Post by i***@gmail.com
Thanks alot Mr Barrow. The query
select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(endpoint > 1) and
(startpoint < 4)
is returing correct results. It fails in this scenario
Suppose StartPoint is 4 and Endpoint is 9 then 22, 24, 26 and 40
should be unavailable. This query only returns 22, 24 and 26 in that
case.

It would help if you would quote previous replies in the thread.
RouteID SeatNumber DateofBooking StartPoint EndPoint
1 26 28/08/2012 1 9
1 40 28/08/2012 1 4 <<--
1 15 28/08/2012 1 2
1 16 28/08/2012 1 2
1 22 28/08/2012 4 9
1 24 28/08/2012 4 9


Why should seat 40 be unavailable to somebody beginning his trip at 4? The
first passenger ends his trip at 4, which I assume means he leaves the plane
at 4, which should result in making his seat available to somebody beginning
his trip at 4, shouldn't it? Why is this not the correct logic? If nobody
could book that seat unless they were starting their journey at 5, that
would imply that you are forcing the seat to be empty during the trip from 4
to 5, wouldn't it?

I cannot provide a different answer until you answer this question. This is
the second time I have asked it.

[i***@gmail.com]

I got confused. You are correct. Seat 40 should be available for trip from 4 to 9. Thanks for your help.

[i***@gmail.com]

select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(startpoint <= 1) and
(endpoint >= 4)

RouteID SeatNumber DateofBooking StartPoint EndPoint
1 26 28/08/2012 1 9
1 40 28/08/2012 1 4
1 15 28/08/2012 1 2
1 16 28/08/2012 1 2
1 22 28/08/2012 4 9
1 24 28/08/2012 4 9

This query isnt returing required results. For example if I have to book seat from StartPoint 1 and endPoint 4 then the unavailable seat should be 15, 16, 26 and 40. However this query is returing SeatNumber 26 and 40.

[Gene Wirchenko]

Post by i***@gmail.com
select SeatNumber from SeatsBooked
where (RouteId = 1) and
(DateofBooking = '28/08/2012') and
(startpoint <= 1) and
(endpoint >= 4)
RouteID SeatNumber DateofBooking StartPoint EndPoint
1 26 28/08/2012 1 9
1 40 28/08/2012 1 4
1 15 28/08/2012 1 2
1 16 28/08/2012 1 2
1 22 28/08/2012 4 9
1 24 28/08/2012 4 9
This query isnt returing required results. For example if I have to book seat from

StartPoint 1 and endPoint 4 then the unavailable seat should be 15,
16, 26 and 40. However this query is returing SeatNumber 26 and 40.

Why?

The where condition includes endpoint>=4 which is not true for
SeatNumber 15 and 16.

You have a similar error in one of your other posts.

Sincerely,

Gene Wirchenko