A query for reserving seats - 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

SQL Server 2005 Forums

Transact-SQL (2005)

A query for reserving seats

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-03-31 : 16:04:59

Assume that i want to register online for an exam and after registration is complete, applicants will receive an entrance ID card.
To do so i have considered the following tables:
Days(day_id,day_name)
Schools(school_id, school _name)
Times(time_id,time_name)
Exame(exam_id,day_id, school _id,time_id,seat,student_id) --exame_id is identity
seats(day_id, school _id,time_id,seat,taken)
The exam will be held in a couple of days, and capacity of each school for each time is 10 students. Seats will be filled by DAY,SCHOOL and TMIE respectively. For example:
Day1, school1, time1; 10 students.
Day1, school1, time2; 10 students.
…
Day1, school1, last time; 10 students.
Day1, school2, time1; 10 students.
Day1, school2, time2; 10 students.
Day1, school2, last time; 10 students.
And so on.
The SEATS table indicates weather seats are taken or not. To enter input i have used the following algorithm:


insert into seats(day_id, school_id, time_id, seat)
SELECT day_id, school_id, time_id,
 
              ROW_NUMBER() OVER(ORDER BY day_id, school_id, time_id, i) AS id
          FROM days 
         CROSS JOIN  schools 
         CROSS JOIN  times 
         CROSS JOIN(SELECT  1 UNION ALL
                    SELECT  2 UNION ALL
                    SELECT  3 UNION ALL
                    SELECT  4 UNION ALL
                    SELECT  5 UNION ALL
                    SELECT  6 UNION ALL
                    SELECT  7 UNION ALL
                    SELECT  8 UNION ALL
                    SELECT  9 UNION ALL
                    SELECT 10) AS d(i)

Now to get the next empty seat i need to run this query:


select top(1) * from seats where taken=0 order by 
day_id,school_id,time_id,seat

so far there is no problem, now i want to add the capacity field to the school table and i do not know what query should be used to fill the seats table.
I’ll appreciate your help.

****<>****

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-03-31 : 23:32:13

please help me

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 01:46:02

it will be easier for other to help if you can post some sample data (in DML form) and the expected result

KH
[spoiler]Time is always against us[/spoiler]

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-02 : 10:05:47

Assume that the data in tables are this:
Days
day_id day_name
1 sunday 2010/03/23
2 Tuesday 2010/03/25
------------------------------------
Schools
school_id school_name
1 school1
2 school2
----------------------------------
Times
time_id time_name
1 9 - 10
2 10 - 11
-------------------------------------
If all seats reserved ,the exam table must be like this :
Exam

Day_id School_id Time_id Personal_id
1 1 1 1
1 1 1 2
1 1 2 3
1 1 2 4
1 2 1 5
1 2 1 6
1 2 2 7
1 2 2 8
2 1 1 9
2 1 1 10
2 1 2 11
2 1 2 12
2 2 1 13
2 2 1 14
2 2 2 15
2 2 2 16

---------------------------------
Consider data in tables days,schools,times , now seats table must be like this.
Day_id School_id Time_id seat taken
1 1 1 1 0
1 1 1 2 0
1 1 2 3 0
1 1 2 4 0
1 2 1 5 0
1 2 1 6 0
1 2 2 7 0
1 2 2 8 0
2 1 1 9 0
2 1 1 10 0
2 1 2 11 0
2 1 2 12 0
2 2 1 13 0
2 2 1 14 0
2 2 2 15 0
2 2 2 16 0
-----------------------------------------
When a clients wants to register ,we must figure out where we can register him?!!so we must find out the first free seat by this query:

select top(1) * from Places where taken=0 order by dayid,schoolid,timeid,seat

and then we use this information for insert into exam table and we update the seats table for new taken seat.(set taken=true)
now,the problem is I don’t now how to fill seats table(a query for initialize table [if we add the capacity field to the schools table])

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-03 : 10:18:40

use this to update the taken and return the assigned Day_id, School_id, Time_id, Seat


; with FreeSeats
as 
(
	select	*, RowID = row_number() over (order by Day_id, School_id, Time_id, Seat)
	from	Seats
	where	Taken	= 0
)
Update	s
set	Taken	= 1
output 	inserted.Day_id, inserted.School_id, inserted.Time_id, inserted.Seat
from	FreeSeats s
where	RowID	= 1

Also refer to your thread in MSDN for the full test script query

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 12:34:46

can the client have any special preference?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-03 : 23:52:58

i very appreciate u dear khtan , but i khnow how to update seats table, the problem is i want to add capacity field to the schools table. it means maybe school1 capacity is 10 person ,but school2 capacity 15 persons.
thanks a lot by the way

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 02:08:26

quote:
Originally posted by mahdi87_gh

i very appreciate u dear khtan , but i khnow how to update seats table, the problem is i want to add capacity field to the schools table. it means maybe school1 capacity is 10 person ,but school2 capacity 15 persons.
thanks a lot by the way

****<>****

Then you should have capacity field in your schools table.
Also I repeat my earlier question, can a client have preffered time like say I prefer the slot 9-10 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 02:54:47

quote:
can a client have preffered time like say I prefer the slot 9-10 etc

no there is no preffered things.
yes first i want add capacity field to the schools table,and then initialize seats table with a query

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 02:57:10

ok so what will be input values? just clientid alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 03:08:01

yes, but i have a stored procedure that first checks client id (personal_id)

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 03:10:12

What all information you receive in procedure? client_id alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 03:36:51

yes, but now i want just a query to fill seats table, actually i want initialize it and then i'll use it [with personal_id] to insert into exam table

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 03:38:39

Thats ok..but how would you relate client_id to a particular school/ slot? or is relationship random?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 03:41:51

no, the client already registered in another table named users, and they must log in to site, then they can register themself in the exam,and i just need to save their personal_id to exame table, and i can find other information on the users table

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 03:44:04

you mean his school,slot etc will be stored in user table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 03:46:59

no, this exams are not for the students, it's for teachers and other official workers in the Amozesh Parvaresh Organization

****<>****

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 03:51:58

[sorry for my bad english speaking]
all information from all tables must be like this:
fname,lname,...,[from users table];day_name[days];school_name[schools];time_name[times];
this information will be shown on the entry card for the exam

****<>****

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-04 : 04:30:12

to generate records for the seats table


	select	d.day_id, s.school_id, t.time_id, 
		Seats	= row_number() over(order by n.number)
	from	Days d
		cross join Schools s
		cross join Times t
		inner join 
		(	
			select	number = n.number + 1
			from	master..spt_values n	
			where	n.type	= 'P'
		) n	on	n.number	<= s.capacity

KH
[spoiler]Time is always against us[/spoiler]

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-04-04 : 04:50:02

thnaks a lot
i'll test this code

****<>****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-04 : 04:55:52

ok see below


--adding capacity column to school

ALTER TABLE schools ADD capacity int

--putting data into capacity fields (pass suitable values for school_id and capacity in below query)

UPDATE school
SET capacity=@capacity
WHERE School_Id=@School_ID

--populate the seats table now

INSERT INTO seat
SELECT d.day_id,s.school_id,t.time_id,0
FROM days d
CROSS JOIN schools s
CROSS JOIN times t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources