challenging query to reorder rows on condition

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)

challenging query to reorder rows on condition

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-08-20 : 22:07:32

Hi,

Some background:
I have a school. School have classrooms. Classrooms are divided into various sections (Section A, Section B and so on) . Sections have subsections. Every student is allocated a rollnumber(unique) in that subsection/section. However student is also given a choice to specify his own roll(DesiredRoll) in that section. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count.
I have two tables : Students and StuHistory. The structure of the Student table is as follows :

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Student](
 [RID] [int] NOT NULL,
 [Class] [int] NULL,
 [Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [SubSection] [int] NULL,
 [RollNo] [int] NULL,
 [DesiredRoll] [int] NULL,
 [TrackingNo] [int] NULL,
 [Original_rollno] [int] NULL,
 [StudentStatus] [int] NULL
)
END
GO

For the first unique desired roll no in that subsection/section the tracking no is always 0.
[StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted).

Challenge :

After every fortnight, i have to run a batchquery that does the following:

1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student.

2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno

So if 4 students have chosen the desired roll no as 5 and their actual roll no are scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted.

How do i write query for this? I have been struggling.

Script :


INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
 VALUES (3, 1, N'A', 1, 3, 1, 1,0,1)

INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (4, 1, N'A', 12, 1, 1, 0,-1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1)
 
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
 VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1)

INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 )
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] )  
VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno],  [StudentStatus] ) 
VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 02:47:18

UPDATE f
SET f.RollNo = f.NewRollNo
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Section, SubSection ORDER BY RollNo) AS NewRollNo) AS f

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-08-21 : 03:34:49

Hi,

Thanks for the reply. I appreciate it.

I get the following error when I run the query

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Section'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'SubSection'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'RollNo

Just to make things clearer..here is the input and the desired output

[RID] [Class] [Section] [SubSection] [RollNo] [DesiredRoll] [TrackingNo] [Original_rollno] [StudentStatus]
1, 1, 'A', 1, 1, 1, 0, 0, 1
2, 1, 'A', 1, 2, 2, 0, 0, 1
3, 1, 'A', 1, 3, 1, 1, 0, 1
4, 1, 'A', 12, 1, 1, 1, 0, -1
5, 1, 'A', 12, 2, 1, 1, 0, 1
6, 1, 'A', 12, 3, 2, 0, 0, 1
7, 1, 'B', 5, 1, 3, 0, 0, 1
8, 1, 'B', 5, 2, 3, 1, 0 ,1
9, 1, 'B', 5, 3, 3, 2, 0, 1
10, 1, 'B', 5, 4, 2, 0, 0, 1
11, 1, 'B', 5, 5, 2, 1, 0, 1
12, 1, 'B', 10, 1, 1, 0, 0, 1
13, 1, 'B', 10, 2, 1, 1, 0, 1
14, 1, 'B', 10, 3, 1, 2, 0, -1
15, 1, 'B', 10, 4, 2, 0, 0, 1

Output after reordering :

1, 1, 'A', 1, 1, 1, 0, 1, 1
3, 1, 'A', 1, 2, 1, 1, 3, 1
2, 1, 'A', 1, 3, 2, 0, 2, 1
5, 1, 'A', 12, 1, 1, 1, 2, 1
6, 1, 'A', 12, 2, 2, 0, 3, 1
10, 1, 'B', 5, 1, 2, 0, 4, 1
11, 1, 'B', 5, 2, 2, 1, 5, 1
7, 1, 'B', 5, 3, 3, 0, 1, 1
8, 1, 'B', 5, 4, 3, 1, 2 ,1
9, 1, 'B', 5, 5, 3, 2, 3, 1

12, 1, 'B', 10, 1, 1, 0, 1, 1
13, 1, 'B', 10, 2, 1, 1, 2, 1
15, 1, 'B', 10, 3, 2, 0, 4, 1

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-21 : 10:01:00

quote:

UPDATE f
SET f.RollNo = f.NewRollNo
FROM (SELECT RollNo, ROW_NUMBER() OVER (PARTITION BY Section, SubSection ORDER BY RollNo) AS NewRollNo FROM dbo.Student) AS f

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:05:06

Thanks!
I guess I was in a hurry...

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-08-21 : 11:12:39

Thanks.

Appreciate the help.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-22 : 04:00:42

No worries - happens to the best of us Peso!

Mark

lols
Posting Yak Master

174 Posts

Posted - 2007-08-22 : 06:33:23

Hi,

How do I handle nulls in [desiredroll] and [trackingno]?

Let us say I have the following data:

RID] [Class] [Section] [SubSection] [RollNo] [DesiredRoll] [TrackingNo] [Original_rollno] [StudentStatus]
1 1 A 1 1 NULL NULL 0 1
2 1 A 1 2 NULL NULL 0 1
3 1 A 1 3 NULL NULL 0 1
4 1 A 12 4 NULL NULL 0 -1
5 1 A 12 5 NULL NULL 0 1
6 1 A 12 6 NULL NULL 0 1
7 1 B 5 1 3 0 0 1
8 1 B 5 2 3 1 0 1
9 1 B 5 3 3 2 0 1
10 1 B 5 4 2 0 0 1
11 1 B 5 5 2 1 0 1
12 1 B 10 1 1 0 0 1
13 1 B 10 2 1 1 0 1
14 1 B 10 3 1 2 0 -1
15 1 B 10 4 2 0 0 1

and now I insert another row with desired roll in that section to be 1

INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (16, 1, N'A', 1, 7, 1, 0, 0, 1)

After running your query I get

1 1 A 1 1 NULL NULL 0 1
2 1 A 1 2 NULL NULL 0 1
3 1 A 1 3 NULL NULL 0 1
4 1 A 12 1 NULL NULL 0 -1
5 1 A 12 2 NULL NULL 0 1
6 1 A 12 3 NULL NULL 0 1
7 1 B 5 1 3 0 0 1
8 1 B 5 2 3 1 0 1
9 1 B 5 3 3 2 0 1
10 1 B 5 4 2 0 0 1
11 1 B 5 5 2 1 0 1
12 1 B 10 1 1 0 0 1
13 1 B 10 2 1 1 0 1
14 1 B 10 3 1 2 0 -1
15 1 B 10 4 2 0 0 1
16 1 A 1 4 1 0 0 1

whereas I had desired the roll no to be 1.

How do I handle nulls?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources