Re-order sequence numbers
-
I have a table containing student course history records, so there are numerous records with the same stulink (student identifier) each with different course info, including a date field and a sequence number. I need to make the seqence numbers reflect the ascending order of the date field. Right now, the tables look like this
Table as it's currently arranged stulink date sequence# 1254 01/02/03 6 1254 01/03/03 3 1254 01/04/03 4 1254 01/05/03 1 1254 01/06/03 2 1254 01/07/03 5 2222 01/02/00 4 2222 02/02/06 1 2222 04/07/06 3 2222 05/01/06 2 I need to re-arrange the sequence numbers so they look like this.......
Desired End Result stulink date sequence# 1254 01/02/03 1 1254 01/03/03 2 1254 01/04/03 3 1254 01/05/03 4 1254 01/06/03 5 1254 01/07/03 6 2222 01/02/00 1 2222 02/02/06 2 2222 04/07/06 3 2222 05/01/06 4 Thanks in advance for any advice on this....
-
-- Create a temp table to re-sequence
Create Table #ReSequence
(
stulink int,
[date] smalldatetime,
Sequence Int Identity
)
-- Insert in stulink/date otder. Table is now sequenced.
-- But each stulink does not start at 1
Insert Into #ReSequence
(stulink, [date])
Select stulink, [date]
From YourTable
Order By stulink, [date]
-- Update table with the new sequence
Update T
-- Force each stulink to start at 1
Set Sequence = (S.Sequence - dtMin.MinPerStudent + 1)
From YourTable As T
Inner Join #ReSequence As S
ON (T.stulink = S.stulink And
T.[date] = S.[date])
-- Derived table to get the lowest sequence per student
Inner Join
(
Select stulink, Min(Sequence) As MinPerStudent
From #ReSequence
Group By stulink
) dtMin
On (T.stulink = dtMin.stulink)
-
-- create table variable
declare @resequence table( keyid integer identity(1,1),
stulink integer,
coursedate datetime)
declare @rowct integer
declare @numrows integer
declare @coursedate datetime
declare @sequenceno integer
declare @curstulink integer
declare @prestulink integer
-- get rows ordered by stulink and date
insert into @resequence
select stulink,
coursedate
from YourTable
order by stulink, coursedate
set @numrows = (select count(*) from @resequence)
set @rowct = 1
set @prestulink = 0
-- go through each row and update with the new sequence number
while (@rowct < @numrows) begin
-- get the current record data
select @curstulink = stulink,
@coursedate = coursedate
from @resequence
where keyid = @rowct
-- reset if the stulink has changed
if (@curstulink <> @prestulink) begin
set @sequenceno = 1
set @prestulink = @curstulink
end
-- update the sequence number based on the stulink and date
update YourTable set
sequenceno = @sequenceno
where stulink = @curstulink
and coursedate = @coursedate
set @rowct = @rowct + 1
set @sequenceno = @sequenceno + 1
end
-
The problem with this proc is that there cannot be duplicate sequence numbers (I just discovered) for a single stulink, so when I try to run this, I get a primary key error when it tries to change a 2 to a 1 for example when there is already a sequence number 1 for that particular stulink..
-
When I ran this, I got this result ...
(497 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2601, Level 14, State 3, Line 39
Cannot insert duplicate key row in object 'ASAH5032' with unique index 'ASAH5032_PRIMARY'.
The statement has been terminated.
Server: Msg 2601, Level 14, State 3, Line 39
Cannot insert duplicate key row in object 'ASAH5032' with unique index 'ASAH5032_PRIMARY'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Is this because I can't have a duplicate sequence number for a particular stulink?
DDL for table below this line...
if exists (select * from dbo.sysobjects where id = object_id(N'[SASI].[ASAH5032]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SASI].[ASAH5032]
GO
CREATE TABLE [SASI].[ASAH5032] (
[STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STULINK] [numeric](5, 0) NOT NULL ,
[SEQUENCE] [numeric](3, 0) NOT NULL ,
[FROMDATE] [datetime] NULL ,
[TODATE] [datetime] NULL ,
[SCHLATTNUM] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLATTNM] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CITY] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROVINCE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTRY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRADELEVEL] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MEMBERSHIP] [numeric](5, 1) NULL ,
[ABSENT] [numeric](5, 1) NULL ,
[PRESENT] [numeric](5, 1) NULL ,
[BYAUTOPROC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERFIELD2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EOYSTATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RESERVED] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FILLER] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHOOLFLAG] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCHLYEAR] [numeric](4, 0) NULL ,
[DISTNO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FLSCHLNO] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERSTAMP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATESTAMP] [datetime] NULL ,
[TIMESTAMP] [numeric](6, 0) NULL ,
[U$DATE] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
-
>> so when I try to run this, I get a primary key error
The only way that could happen with the code I posted is if there are more than 1 record with the same date for a given stulink.
You sample data indicated that this was not the case - which is the danger of posting just sample data and not table DDL.
So back to your original posting - is it possible for a stulink to have 2 records wiht the same date ?
-
If you need to do it only once or if you don't have performance issues I would use a cursor
Regards,Yelena Varsha
-
Well now ... sorry ... as it turns out.. I now find out from the owner of the data that it is possibe for there to be a duplicate date with a particular stulink.... and in that case, we would sort by another field... that being "schoolum" then "sequence".
Uhhg..sorry...
-
First, using a cursor
or a WHILE loop 
just isn't an option for me... I just gotta do things in a set based fashion 
. Here's a self-supporting test that emulates the problem and solves it... the comments say it all 
--===== If temporary test table exists, drop it
IF OBJECT_ID('TempDB..#ASAH5032') IS NOT NULL
DROP TABLE #ASAH5032
GO
--===== Create the temporary test table
CREATE TABLE #ASAH5032
(
StuLink NUMERIC(5,0) NOT NULL,
SchoolNum VARCHAR(3) NOT NULL,
Date DATETIME NOT NULL,
Sequence NUMERIC(3,0) NOT NULL
)
--===== Add a composite primary key
ALTER TABLE #ASAH5032
ADD CONSTRAINT ASAH5032_PRIMARY
PRIMARY KEY (StuLink, Date, SchoolNum, Sequence)
--===== Populate the table with sample data including a couple dupe dates
INSERT INTO #ASAH5032 (StuLink, SchoolNum, Date, Sequence)
SELECT 1254,'1','01/02/03',6 UNION ALL
SELECT 1254,'1','01/03/03',3 UNION ALL
SELECT 1254,'1','01/04/03',4 UNION ALL
SELECT 1254,'1','01/05/03',1 UNION ALL
SELECT 1254,'1','01/06/03',2 UNION ALL
SELECT 1254,'1','01/07/03',5 UNION ALL
SELECT 2222,'1','01/02/00',4 UNION ALL
SELECT 2222,'2','01/02/06',1 UNION ALL
SELECT 2222,'3','01/02/06',3 UNION ALL
SELECT 2222,'1','05/01/06',2
--========================================================================
--===== Test setup complete... begin the demo for the problem solution
--========================================================================
--===== Disable the primary key
ALTER TABLE #ASAH5032
NOCHECK CONSTRAINT ASAH5032_PRIMARY
--===== Do the update without RBAR (Row by agonizing row)
UPDATE #ASAH5032
SET Sequence = d.NewSequence
FROM #ASAH5032 m,
(SELECT t1.StuLink, t1.SchoolNum, t1.Date,
(SELECT COUNT(*)
FROM #ASAH5032 t2
WHERE t2.StuLink = t1.StuLink
AND CONVERT(VARCHAR(8),t2.Date,112)+t2.SchoolNum
<= CONVERT(VARCHAR(8),t1.Date,112)+t1.SchoolNum
) AS NewSequence
FROM #ASAH5032 t1
)d
WHERE m.StuLink = d.StuLink
AND m.SchoolNum = d.SchoolNum
AND m.Date = d.Date
--===== Re-enable the primary key
ALTER TABLE #ASAH5032
CHECK CONSTRAINT ASAH5032_PRIMARY
--===== Display the resequenced result
SELECT *
FROM #ASAH5032
ORDER BY StuLink, Date, SchoolNum
Now, I also have to say that I looked at the schema for the table and I gotta say that using things like NUMERIC(3,0) have me confused... NUMERIC(3,0) take 5 bytes of storage and only got to 999... SMALLINT only takes 2 bytes of storage and goes up to 32,767.
I know you're probably not the one that designed the table but, Holy Cow, what a mess someone made!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Excuse me if I have mis-read your original post but if you just want the output in the order you specified then you could just use the ORDER BY clause IE:
SELECT * FROM studenttable ORDER BY stulink, sequence
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
You didn't mis-read.. .I didn't adequately communicate my information. The table needs to be re-indexed ..(rather than simply output) so users will see the information from the table in the correct manner... via vendor designed SP's and a non-customizable proprietary GUI.
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply