I have finished a Database for my work which shows classroom seat availabilityand a booking system.#


I have one last thing to do and that is to make a graphical view on a webpage(using DreamweaverMX) to show at a glance whether a seat is booked or available.

I would apprecite help as I am fast running out of hair to tear out.
The quey or stored proc comes from a table.

Session_booking table consists

Session_date smalldatetime ( which would be the only input parameter I would wish to use.)

Session _Name varchar(10) ie: AM, PM, EVENING

Seat_Number smallint ie; 1,2,3,4,5,6,7,8,


I want to show that on a given date that in the Session_name AM that seat 1 is booked or available.

Many Thanks

Mike

Are you stating then that if an entry exists in the Session_booking table for a given seat number the seat number is booked, otherwise it is available?

Are you intending to provide a seat number as a parameter and have the procedure return whether the seat is available?

Assuming that the presence of an entry indicates the seat is booked, how about something like this which returns a 1 if a given seat is available or 0 if it is not:

Create Procedure IsSeatAvailable
   @TheDate smalldatetime,
   @Session varchar(10),
   @Seat smallint
as
   set nocount on
   if exists (SELECT Seat_Number FROM Session_Booking
                 WHERE Session_date=@TheDate
                 AND Session_Name=@Session
                 AND Seat_Number = @Seat) RETURN 0
   RETURN 1

If instead you are looking for a query to give you ALL seats and whether they are available or not, that is a different story. The easiest way to do that is to create a table which contains all the seat numbers then use an outer join as, perhaps:

SELECT AllSeats.Seat_Number,
       CASE WHEN SB.Seat_Number IS NULL THEN 'available'
       ELSE 'allocated' END as SeatAvail
   FROM AllSeats LEFT JOIN Session_booking SB ON AllSeats.SeatNumber=SB.SeatNumber
   WHERE SB.Session_date=@TheDate
   AND SB.Session_Name=@Session

Note that it might be better to include ALL seats in the booking table with an explicit indicator which states whether the seat is available. Then everything is easy.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks

I want to calculate on all seats. The thing is that we have 3 x sessions that a seat can be booked for.

AM 1, 2, 3
PM 1, 2, 3
Evening 2, 3.

So I want to be able to have this data available so I can form a graphical webpage.

The only thing I want to input is the date.

Mike