Best way to update data - 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 2000 Forums

Transact-SQL (2000)

Best way to update data

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-10-27 : 01:15:29

I have a table called Students that has 10 class columns in it that hold the ClassID's that the students have chosen to take.

The Classes are being held over 3 days in 10 time slots (1 to 10). The same ClassID can occur in multiple time slots. These are stored in the Schedule table.

The Students table has 10 TimeSlot columns that need to be populated with ScheduleID that comes from the Schedule Table - but I need to take into account classes that already have been placed into that timeslot for a student, and i also have to check that the maxseats has not gone over the maximum value.

Because the same class can occur in multiple time slots I am not sure what the best way is to shift around the classes in the available time slots to make sure that as many classes as possible can be scheduled/made to fit for the student.

For example, if a student fits into TimeSlot1 for ClassID1, but we can move that class into TimeSlot3 so that we can fit ClassID 3 into TimeSlot1 (for whatever reason) then we would need to do it.

Hopefully I am making sense and someone can give me some guidance on the best way to do this. I didn't create these table structures. Thanks.

CREATE TABLE [dbo].[Classes] (
[ClassID] [int] ,
[ClassName] [char] (50)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] ,
[TimeSlot] [int] ,
[ClassID] [int] ,
[MaxSeats] [int]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Students] (
[StudentID] [int] ,
[StudentName] [char] (50) ,
[Class1] [int] ,
[Class2] [int] ,
[Class3] [int] ,
[Class4] [int] ,
[Class5] [int] ,
[Class6] [int] ,
[Class7] [int] ,
[Class8] [int] ,
[Class9] [int] ,
[Class10] [int] ,
[TimeSlot1] [int] ,
[TimeSlot2] [int] ,
[TimeSlot3] [int] ,
[TimeSlot4] [int] ,
[TimeSlot5] [int] ,
[TimeSlot6] [int] ,
[TimeSlot7] [int] ,
[TimeSlot8] [int] ,
[TimeSlot9] [int] ,
[TimeSlot10] [int]
) ON [PRIMARY]
GO

Insert Classes (ClassID, ClassName) select 1, 'ClassA'
Insert Classes (ClassID, ClassName) select 2, 'ClassB'
Insert Classes (ClassID, ClassName) select 3, 'ClassC'
Insert Classes (ClassID, ClassName) select 4, 'ClassD'
go

Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 1, 1, 1, 50
Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 2, 3, 1, 50
Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 3, 1, 2, 20
Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 4, 7, 2, 25
Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 5, 4, 3, 20
Insert Schedule (ScheduleID, TimeSlot, ClassID, MaxSeats) select 6, 4, 1, 30
go

Insert Students (StudentID, StudentName, Class1, Class2, Class3) select 1, 'Peter', 1, 2, 0
Insert Students (StudentID, StudentName, Class1, Class2, Class3) select 2, 'John', 3, 2, 0
Insert Students (StudentID, StudentName, Class1, Class2, Class3) select 3, 'Sally', 4, 1, 0
Insert Students (StudentID, StudentName, Class1, Class2, Class3) select 4, 'Chris', 1, 2, 3
go

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-05 : 07:55:41

What is your expected result based on the above sample data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources