Help with Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 09:44:22

How would I write a stored procedure to check to see


Loc          TrainDte             Times   MaxRmSize   
ClassA	  7/26/2010 12:00:00 AM	   2:00	    25
ClassB	  7/26/2010 12:00:00 AM	   11:00    15
ClassC	  7/26/2010 12:00:00 AM	   10:00    25

on the front end I will have the loc in a dd, traindte in a dd and time I want to have a something come up with how many spaces are left in the room for that date and time. How could I write that?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 10:02:42

Okay left some info out....

Here are the two tables:

TraineeInfo
Fname
Lname
Component
Locataion
TrainDate
Times

Training
Location PK varchar(25)
TrainDate PK datetime
Times PK varchar(50)
MaxRoomSize char(2)

Here's the Training Table


Loc          TrainDte             Times   MaxRmSize   
ClassA	  7/26/2010 12:00:00 AM	   2:00	    25
ClassB	  7/26/2010 12:00:00 AM	   11:00    15
ClassC	  7/26/2010 12:00:00 AM	   10:00    25

Here's the TraineeInfo Table


Fname   Lname  component  Location   TrainDate     Times
Jane	Smith	RO	  ClassA    7/26/2010      2:00
Brenda	Jones	FO	 ClassA	    7/26/2010 	   2:00

How would I write a stored procedure to count how many people are in classA, going to training 7/26/2010 at 2:00, would this work?

Select count(*) a.Location, a.TrainDate, a.Times
from TraineeInfo a
Join training b on b.location = a.location

from Tr

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 10:11:00

Never mind I got it:

select count(*) as Ava
from TraineeInfo
where location='classa' and traindate='7/26/2010' and times='2:00'

I'm sure I'll be back...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-19 : 10:14:06

Ugh...I'm back already

This shows me the count of 2 signed up for this class.

select count(*) as Ava
from TraineeInfo
where location='classa' and traindate='7/26/2010' and times='2:00'

There are 25 seats in the class how can I write a procedure to show there are 23 seats left?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-20 : 14:12:55

Hello someone helped me with this:

ALTER procedure [dbo].[GetSeatsLeft] --'6'

@trainid int
AS
select t.trainid, ti.seatstaken, t.maxroomsize - ti.seatstaken as seatsleft
from (select Count (trainid) as seatstaken, trainid
from traineeinfo
group by trainid) ti
join TSRPtraining t
on t.trainid = ti.trainid
where ti.trainid = @trainid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources