• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

RE : Sequential number generation

Hi,

I am looking for SQL query which will generate the sequential number in below format

YYYYMMDDXXXXX like 2012082900001 -> XXXXX will be an sequential incremented number with no duplicates as per YYYYMMDD and no skipping of sequential number (for audit purpose) in table

What is the best approach to do so keeping in mind the performance and speed
0
vikalgupta
Asked:
vikalgupta
  • 6
  • 3
  • 3
1 Solution
 
CluskittCommented:
SELECT RIGHT(MAX(MyField),5)+1
FROM Table
WHERE MyField LIKE CONVERT(char(8),@MyDateVar,112)+'%'

@MyDateVar may also be a date field.
0
 
CluskittCommented:
To ensure you always get 5 digits:
SELECT RIGHT('00000'+CAST(RIGHT(MAX(MyField),5)+1 AS varchar(6)),5)
FROM Table
WHERE MyField LIKE CONVERT(char(8),@MyDateVar,112)+'%'
0
 
Habib PourfardSoftware DeveloperCommented:
The following query gives you what you are looking for on daily basis:
SELECT CONVERT (CHAR(8) , GETDATE(), 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX([YourSequentialNumberColumn]),'0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) FROM [YourTable]
WHERE SUBSTRING([YourSequentialNumberColumn], 1, 8) = CONVERT (CHAR(8) , GETDATE(), 112)

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
vikalguptaAuthor Commented:
Hi Purfard,

Thanks for your reply.

How could i handle if 2 users simultaneously hit my table to insert order number, how can i protect users to insert same duplicate value in table.
0
 
CluskittCommented:
Make the query part of your insert. For example, instead of INSERT INTO Table VALUES (val1,val2,etc), turn it into INSERT INTO Table SELECT val1,val2,etc FROM Table
This way, you can use the formula to make sure that the insert will be unique.
0
 
vikalguptaAuthor Commented:
Hi Purfard,

Duplicacy is happenning if i generate the no.s in multiple sql query window even after using the CTE to get order number. below is the query i used


declare @rownum int
set @rownum=0
while @rownum<10000
begin
      with cte1(orderid)
      as
      (

      SELECT CONVERT (CHAR(8) , GETDATE()+1, 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(
              orderid),'0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) as Orderid
              FROM test
              WHERE SUBSTRING(orderid, 1, 8) = CONVERT (CHAR(8) , GETDATE()+1, 112)
      )
      insert into TEST(orderid) select orderid from cte1
      set @rownum=@rownum+1
end
0
 
vikalguptaAuthor Commented:
Hi Purfard,

any update?
0
 
Habib PourfardSoftware DeveloperCommented:
Hi
I tried your query and it worked fine for me without any duplicate orderids.
As another solution I created a trigger:
CREATE TABLE [dbo].[Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderID] [char](13) NULL,
	[Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO 

CREATE TRIGGER AutoGenerateOrderID ON Test
  AFTER INSERT
AS
  BEGIN
    SET NOCOUNT ON ;

    UPDATE  Test
    SET   OrderID = ( SELECT  CONVERT (CHAR(8), DATEADD(dd, 1, GETDATE()), 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(orderid), '0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) FROM Test WHERE SUBSTRING(OrderID, 1, 8) = CONVERT (CHAR(8), DATEADD(dd, 1, GETDATE()), 112) )
    WHERE Test.ID = ( SELECT  id FROM  inserted )
  END

GO

Open in new window


To Test it:
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
GO

Open in new window

0
 
vikalguptaAuthor Commented:
Hi Purfard,

Please make the order id as primary key and try to execute 1000 order ids in 3 SQL query window. you will get the primary key violation error
0
 
Habib PourfardSoftware DeveloperCommented:
I added TABLOCKX Lock and it worked:
DECLARE @rownum INT
SET @rownum = 0
WHILE @rownum < 10000
    BEGIN 
        WITH    cte1 ( orderid )
                  AS ( SELECT   CONVERT (CHAR(8), GETDATE() + 1, 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(orderid), '0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) AS Orderid
                       FROM     Test
                       WHERE    SUBSTRING(orderid, 1, 8) = CONVERT (CHAR(8), GETDATE() + 1, 112)
                     )
            INSERT  INTO Test WITH (TABLOCKX ) ( orderid )
                    SELECT  orderid
                    FROM    cte1
        SET @rownum = @rownum + 1
    END

Open in new window

0
 
vikalguptaAuthor Commented:
Any other solution apart from making table lock?
0
 
vikalguptaAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now