question

meallen2 avatar image
meallen2 asked

Dynamically Generate a Sequence Number Based on Date field, with restart sequence for each Year

Hello, I have a data set where I need to generate a specific unique ID using a concatenation of literal values, YYYY of a date field, and a query generated sequence number. example: I have two columns MatterName and CreatedOn MName CreatedOn Matter A 6/12/2014 16:09 Matter B 7/23/2014 16:04 Matter X 8/24/2015 13:32 Matter D 8/24/2015 14:29 Matter F 1/12/2016 17:52 Matter S 2/1/2016 15:01 Matter M 2/10/2016 15:01 Matter J 5/20/2016 17:16 Matter K 8/30/2016 19:42 Matter Y 9/30/2016 20:00 I need a unique ID equal to the following: SELECT 'MA - D - ' + DATEPART(YYYY, CreatedOn) + ' - ' + mSeq as MNum Where mSequence is generated by creating a 5 digit sequence based on CreatedOn ASC and restarting the sequence for a new year: mSeq MName CreatedOn 00001 Matter A 6/12/2014 16:09 00002 Matter B 7/23/2014 16:04 00001 Matter X 8/24/2015 13:32 00002 Matter D 8/24/2015 14:29 00001 Matter F 1/12/2016 17:52 00002 Matter S 2/1/2016 15:01 00003 Matter M 2/10/2016 15:01 00004 Matter J 5/20/2016 17:16 00005 Matter K 8/30/2016 19:42 00006 Matter Y 9/30/2016 20:00 The end result I am looking for is something like this: Mnum MName CreatedOn MA - D - 2014 - 00001 Matter A 6/12/2014 16:09 MA - D - 2014 - 00002 Matter B 7/23/2014 16:04 MA - D - 2015 - 00001 Matter X 8/24/2015 13:32 MA - D - 2015 - 00002 Matter D 8/24/2015 14:29 MA - D - 2016 - 00001 Matter F 1/12/2016 17:52 MA - D - 2016 - 00002 Matter S 2/1/2016 15:01 MA - D - 2016 - 00003 Matter M 2/10/2016 15:01 MA - D - 2016 - 00004 Matter J 5/20/2016 17:16 MA - D - 2016 - 00005 Matter K 8/30/2016 19:42 MA - D - 2016 - 00006 Matter Y 9/30/2016 20:00
datetimesql-server-2014sequencegenerategenerate-sequence
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Erm... OK. Currently not got a SQL2014 installation handy, but something like this might get you going in the right direction: WITH myData as ( SELECT MName, CreatedOn, DATEPART(year, CreatedOn) AS CreatedYear, ROW_NUMBER() OVER (PARTITION BY DATEPART(year, CreatedOn) ORDER BY CreatedOn) AS SeqNo FROM myTable ) SELECT MName, CreatedOn, 'MA - D - ' + convert(char(4), CreatedYear) + ' - ' + RIGHT( '00000' + convert(varchar(5), SeqNo), 5) as MNum FROM myData Check the [documentation for the ROW_NUMBER() function][1] for more about how it works. Reminder: untested. [1]: https://msdn.microsoft.com/en-us/library/ms186734.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

meallen2 avatar image meallen2 commented ·
Awesome thanks, @ThomasRushton - was able to tweak your sample and worked it into exactly what I was looking for. Appreciate the quick response.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.