Using Identity column to find sequence

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

General SQL Server Forums

New to SQL Server Programming

Using Identity column to find sequence

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-02 : 16:49:58

Hi, I have a requirement to populate a field called CREATE_NUMBER in a table. This field is nothing but the concatenation of the Date+Time+SequenceNumber. The sequence number is just 2 digits and resets itself after 99.

For example, the CREATE_NUMBER for the 11th row will be
'2009020216501411'

For the 120th row, it will be
'2009020216501420'

For the 1199th row, it will be
'2009020216501499'

Is there a way to get this value using a single SELECT statement. This table also has an Identity Column (ROW_NUMBER) if that might help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-02 : 16:56:32

well you can just use your identityColumn mod 100 get the last 2 digits:


select 11 % 100
       ,120 % 100
       ,1199 % 100

----------- ----------- -----------
11          20          99

But do you care if there are sequence breaks in there? ie: if someone deletes rowid=35 can all the existing values stay as they are?

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-02 : 17:00:59

No ..i dont care if one row gets deleted in the middle ..the others can stay as such.

But the table wont have just 1000 rows, it can have millions. I dont think mod100s work on this number too, or will it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-02 : 17:10:31

try it out (as I have in the example select above)