Greetings !!
I am facing a challenge and hope some one can help me with the query.
I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:
Section A
Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3
Student 4 Roll no 4 and so on.
Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A
So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.
How do I write a query for this. I need to run this as a batch process every fortnight.
Thanks
Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.
Solution for SQL Server 2005,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
;WITH CTE
as
(
Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo
from #data
)
Update
CTE
Set
RollNo = NewRollNo
Where
RollNo <> NewRollNo
|||For SQL Server 2000,
Code Snippet
Create Table #data (
[RId] int ,
[Class] int ,
[Section] Char ,
[RollNo] int
);
Insert Into #data Values('1','1','A','1');
Insert Into #data Values('2','1','A','2');
Insert Into #data Values('3','1','A','3');
Insert Into #data Values('4','1','A','4');
Insert Into #data Values('5','1','A','5');
Insert Into #data Values('6','1','A','6');
Insert Into #data Values('7','1','B','1');
Insert Into #data Values('8','1','B','2');
Insert Into #data Values('9','1','B','3');
Insert Into #data Values('10','1','B','4');
Insert Into #data Values('11','1','B','5');
Insert Into #data Values('12','1','B','6');
Insert Into #data Values('13','1','B','7');
Insert Into #data Values('14','1','B','8');
Insert Into #data Values('15','1','B','9');
--Transfer the Student 4 from A section to B section
Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4
--To fill the current gap
Update #data
Set
RollNo = (Select Count(*) From #data Sub
Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)
|||Thanks Manivannan for the solution. I will try the solution and revert back.
Regards,
Lalit
|||Hi,
Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.
I hope you help me out. Thanks a ton Manivannan.
|||The above query is capable to handle multiple calssrooms also..
