Click here to Skip to main content
14,982,215 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table in which we need to create a unique ID 15 digit. But It should be auto incremented. Suppose My unique Id is something like
"M02192015001"
It should get incremented based on date and also last three digits like
"M02202015002"
If there is one more entry on same date it should be like
"M02212015003"

Now I want to know ideas to develop such requirement. Should I get the existing number from DB and simply increment by 1 if it is on same date? Or is there anything other that?

Can anyone give me some ideas for these kind of custom auto increment ID?
Posted

In my opinion the proble is that you have put the three separate things into a single column. Break the data into three columns:
- prefix (containing M) datatype varchar?
- itemdate containing the date dtatype date
- counter datatype int, always incremented by 1 within a date
then define a unique constraint on these three columns to ensure to never have a duplicate.

When showing the custom id just concatenate and format the values of these three columns.

However, if you don't really need a custom ID like this I would suggest using uniqueidentifier[^]. It's guaranteed to be unique and you don't have to worry so much about generating the value.
   
v2
Comments
sudevsu 19-Feb-15 10:45am
   
Thank you so much I already have unique ID. I am specific to 15 digit becoz we need to print that on Stickers for Bar code purpose. But I like the idea of using normalization splitting the column in three different columns and concatenate them when displayed or used
Wendelius 19-Feb-15 11:52am
   
Glad it was helpful :)
CHill60 19-Feb-15 17:35pm
   
5*
Wendelius 20-Feb-15 2:06am
   
Thank you :)
Santosh K. Tripathi 20-Feb-15 1:23am
   
5* from me also :)
Wendelius 20-Feb-15 2:06am
   
Thanks :)
I was going to post this as a comment to solution 1 but it's easier to get the formatting with a "solution" than a comment ...

You said you already had a unique ID and I thought a bit more about the "unique counter within a date". This CP article gave me the idea - How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

Using that principle I wanted to create a unique-within-a-date identifier...

I created a sample table with this
SQL
CREATE TABLE [dbo].[CP1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [recDate] [date] NOT NULL,
    [someData] [nchar](10) NULL,
    [NewId] [int] NULL
) ON [PRIMARY]
I won't repeat any sample data I inserted (unless you ask me to) but the thing I was trying to get to was not having to generate another unique ID.

This sql query will "format" your unique ID based on the date and identity column
SQL
WITH T1(ID, recDate, someData, NewId)
AS
(
    SELECT [ID],recDate,someData,
     ROW_NUMBER() over(PARTITION BY recDate
                         ORDER BY ID ASC) Number
    FROM CP1
)
select ID, recDate, someData, 'M' +
        REPLACE(CONVERT(CHAR(10), recDate, 103), '/', '')
        + Replace(Str(NewId, 7), ' ' , '0')
        from T1 where ID=@input
where ID=@input is however you want to identify the record you are after.

You could put this into a insert trigger on the table to generate the ID and persist it on the table, or use it when extracting for display.

One important caveat - if the ID is generated at display time only, it would change if records had been deleted. If persisted (via a trigger) then it should work regardless.

Please note this is meant to be complementary to Solution 1 - not an alternative
   
Comments
Wendelius 20-Feb-15 2:06am
   
Good addition, +5.
CHill60 20-Feb-15 4:34am
   
Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900