[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • 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 PourfardCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 PourfardCommented:
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 PourfardCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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