NEW SEQUENCE NUMBER DAILY?

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  April 5, 2011 at 4:30 pm

  #1307758

  DMS11X (4/4/2011)

  

  Can someone mark this thread as solved? So if anyone else needs help...

  Looking at your sample resultset above, it seems to me that the column SequenceNumber is derived from the primary key and transaction date. If it is essentially for display purposes only, then perhaps it can just be calculated by ranking SequenceId within SequenceDate. For example:

  CREATE TABLE temp.CFS

  (

  SequenceId int NOT NULL primary key,

  SequenceDate datetime NOT NULL

  );

  insert into temp.CFS (SequenceId,SequenceDate)

  select 1, '2011-04-04' union all

  select 2, '2011-04-04' union all

  select 3, '2011-04-04' union all

  select 4, '2011-04-05' union all

  select 5, '2011-04-05' union all

  select 6, '2011-04-06';

  select

  SequenceId, SequenceDate,

  'CFS-'

  +convert(varchar(30),SequenceDate,112)

  +'-'

  +right('000'+cast(rank() over (partition by SequenceDate order by SequenceId asc) as varchar(9)),3)

  as SequenceNumber

  from temp.CFS

  order by SequenceId;

  SequenceId SequenceDate SequenceNumber

  ----------- ----------------------- --------------------------------------

  1 2011-04-04 00:00:00.000 CFS-20110404-001

  2 2011-04-04 00:00:00.000 CFS-20110404-002

  3 2011-04-04 00:00:00.000 CFS-20110404-003

  4 2011-04-05 00:00:00.000 CFS-20110405-001

  5 2011-04-05 00:00:00.000 CFS-20110405-002

  6 2011-04-06 00:00:00.000 CFS-20110406-001

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Brandie Tarvin

  SSC Guru

  Points: 173068

  More actions

  April 6, 2011 at 4:54 am

  #1307918

  Now that's a clever solution. I like the use of Rank, Partition, and Right there.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  April 6, 2011 at 7:50 am

  #1308022

  Brandie Tarvin (4/6/2011)

  ---

  Now that's a clever solution. I like the use of Rank, Partition, and Right there.

  One caveat to using the ranking method is that each row is ranked dynamically based on it's ordinal position in the resultset, not the entire table. So, depending on how the resultset is filtered, the same row can be assigned a different SequenceNumber. For example, if we take the example I provided above and SELECT WHERE SequenceId = 3, then the row's SequenceNumber is now calculated as CFS-20110404-001 instead of CFS-20110404-003. Depending on the purpose of SequenceNumber, maybe that's the intended result. However, if SequenceNumber is intended to be static, basically acting as an intelligent key, then the ranking method can still be used to update a SequenceNumber column in the table each time a new row is inserted. I'd rather use ranking to update the next value for SequenceNumber than rely on a scheduled job to re-seed an identity or increment a counter somewhere, it just seems cleaner.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho