Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 9th, 2003, 01:53 PM
Registered User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default TSQL to get column values

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
Reply With Quote
  #2 (permalink)  
Old June 9th, 2003, 03:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 9th, 2003, 04:15 PM
Registered User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get the Sum of all the values in a column.. jhansib4u ADO.NET 1 December 1st, 2007 09:15 PM
Modifying column values BabyToy BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 June 18th, 2007 10:15 PM
Datagrid column values krishnasamaga C# 2005 1 August 8th, 2006 06:54 AM
Concatenating column values arnabghosh Access 1 September 21st, 2005 06:45 AM
Datagrid column values pankaj_daga ASP.NET 1.0 and 1.1 Basics 4 November 17th, 2003 05:51 PM



All times are GMT -4. The time now is 12:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.