Results 1 to 9 of 9

Thread: Sqlquery

  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Sqlquery

    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.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select SeatNumber from MyTable
    where (RouteId = @RouteId) and
          (DateofBooking = @DateofBooking) and
          (startpoint <= @startpoint) and 
          (endpoint >= @endpoint)
    Hope this helps.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    imex, why the parentheses???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi r937,

    To be honest I have the habit of always using parentheses to delimit the conditions when there is more than one.
    Do you think it could cause some kind of problem?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by imex View Post
    Do you think it could cause some kind of problem?
    no, they (just (get in (the way)))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    r937,
    you are cool!

    Do (you) think (it (could cause) (some (kind of problem)))?
    no, they (just (get in (the way)))

  7. #7
    Join Date
    Aug 2012
    Posts
    2
    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.
    Last edited by navkumar; 08-23-12 at 08:28.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excessive and gratuitous use of parenthesis does more than get in the way. For complex queries with conditional criteria, it can become confusing to the point where coding mistakes are made, and debugging is difficult.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by navkumar View Post
    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.
    navkumar, the query is returning *exactly* what you told it to return. As was pointed out in several posts, parentheses can be confusing. Why don't you really think about your where clause as you have it, and see if you can explain the results you are seeing.

    In the mean time, do you have more than one bus?(or train, cab, horse, whatever). If so, if need be, in order to maximize your bookings, wouldn't you want to have layovers, or vehicle changes where possible. It is no fun, but there are people who have few options, so why not make partial trips where available, and where you are able to connect the dots with layovers? You could try something like this (and you have more situations than I cover, this was just to get you started).

    *** Notice how I have prepared all of it into one readily consumable script, including object creation, sample data, and the code. You are going to get way better answers if you put a little more effort into helping others to help you. ***

    Code:
    USE tempdb
    GO
    --==== Drop your test table if it exists
    IF OBJECT_ID('tempdb..MyTable','u') IS NOT NULL
    	DROP TABLE tempdb..MyTable
    GO
    
    IF OBJECT_ID('tempdb..spBooking','p') IS NOT NULL
    	DROP PROCEDURE spBooking
    GO
    
    --==== Create your test table
    CREATE TABLE tempdb..MyTable
    	(
    	 RouteID		INT NOT NULL,
    	 SeatNumber		INT NOT NULL,
    	 DateOfBooking	DATE NOT NULL,
    	 StartPoint		INT NOT NULL,
    	 [EndPoint]		INT NOT NULL
    	 )
    
    --==== Populate your test table	 
    INSERT INTO tempdb..MyTable VALUES
    (1,10,'8/08/2012',1,3),
    (1,11,'8/08/2012',1,2), 
    (1,12,'8/08/2012',3,7),
    (1,15,'8/08/2012',2,4),
    (1,20,'8/08/2012',4,8), 
    (1,40,'8/08/2012',1,9), 
    (1,15,'8/28/2012',1,2),
    (1,16,'8/28/2012',1,2),
    (1,22,'8/28/2012',4,9),
    (1,24,'8/28/2012',4,9),
    (1,26,'8/28/2012',1,9),
    (1,40,'8/28/2012',1,4)
    GO
    	
    CREATE PROCEDURE spBooking (@RouteID INT, @DateOfBooking DATE, @StartPoint INT, @EndPoint INT)
    AS
    SELECT
    	RouteID,
    	SeatNumber,
    	DateOfBooking,
    	StartPoint,
    	[EndPoint],
    	Availability = CASE WHEN [EndPoint] <= @StartPoint OR
    							 StartPoint >= @EndPoint
    						THEN 'Seat ' + CAST(SeatNumber AS VARCHAR) + ' is available for full trip'
    						WHEN StartPoint <= @StartPoint AND	
    							 [EndPoint] >= @EndPoint
    						THEN 'Seat ' + CAST(SeatNumber AS VARCHAR) + ' is booked for full trip'
    						WHEN [EndPoint] < @EndPoint 
    						THEN 'Seat ' + CAST(SeatNumber AS VARCHAR) + ' is booked until point ' 
    						            + CAST([EndPoint] AS VARCHAR)
    						WHEN StartPoint > @StartPoint 
    						THEN 'Seat ' + CAST(SeatNumber AS VARCHAR) + ' is only available until point '
    									 + CAST(StartPoint AS VARCHAR)
    						ELSE NULL
    					END 
      FROM tempdb..MyTable
     WHERE DateOfBooking = @DateOfBooking
       AND RouteID = @RouteID
    GO   
     
    EXEC spBooking 1,'8/28/2012',1,4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •