Need help with sequence column that repeats

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)

Need help with sequence column that repeats

Author

Topic

hammb
Starting Member

1 Post

Posted - 2007-03-06 : 12:14:38

Here is what I cant figure out...

How to create a sequence column that starts over for each account number sorted by the time

Simply put I have 3 columns:
Time_Of_Contact DateTime, Account_Nbr Char(11), SEQN Char(2)

I want to populate the third column with a sequence number

for example: *this is the column
that I need help
creating

Time_Of_Contact Account_Nbr SEQN
2007-01-16 10:51:18.000 11111111111 1
2007-01-16 10:52:16.000 11111111111 2
2007-01-16 10:53:18.000 11111111111 3
2007-01-16 10:50:15.000 22222222222 1
2007-01-16 10:52:16.000 22222222222 2
2007-01-16 10:51:16.000 33333333333 1
2007-01-16 10:50:19.000 44444444444 1

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 12:21:39

For a start, use an integer type not char.
You can't just declare it into the table, you'll have to do it with the query you use to insert into the table, basically get the max of the sequence column for the given account and add 1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 13:01:56

update t
set t.seqn = (select count(*) from @table1 as x where x.nbr = t.nbr and x.time_of_contact <= t.time_of_contact)
from table1 as t

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources