I have almost 10 token machines where customers are getting token numbers from table Token. I am using a stored procedure for accessing this table and updating it
Id Name TokenFrom TokenTo LastUsedToken
----------------------------------------------------
1 Token 551 999 562
I have notices that during rush hours a lot of customers are getting duplicate numbers. Maybe this problem is showing up because 10 customers are getting tokens at the same time/hour/second.
Here is issuedToken table
Id PosId Barcode Date Status Number isTaken
1 2 6282019214317 2016-10-20 09:41:45.020 1 366 1
2 2 6282019215918 2016-10-20 09:42:15.020 1 367 1
3 2 6282019225016 2016-10-20 09:42:45.020 1 368 1
4 3 6282019230812 2016-10-20 09:42:55.020 1 369 1
Even sometimes same number is coming on two cashier machines also. I am getting and updating Next Token Number on POS using this Update statement
UPDATE POS
SET tNo = (SELECT TOP 1 NUMBER
FROM Tickets
WHERE STATUS = 1
AND isTaken = 1
AND PosId = (SELECT CGROUP
FROM POS
WHERE NAME='ABC'))
WHERE NAME = 'ABC'
I have 3-3 POS in one group that's why selecting cGroup and in table it's PosId.
I have asked question related to this question before in this question, where someone help me to write a stored procedure for accessing Token Number easily.
But still I am having duplication issue. Can anyone tell me what the best way to avoid duplication ?
What is Best Approach for Auto Increament
Here is something that works in SQL 2008 but does not take into account groupings, does not reset, and has a different formula for barcode
This is the token issued table. Inserting a record in here 'reserves' the token number:
CREATE TABLE [dbo].[issuedToken2](
[Token] [int] IDENTITY(1,1) NOT NULL,
[Barcode] AS (((6820000000.)+[Token])*(100)+[PosID]),
[GenerationDate] [smalldatetime] NOT NULL,
[PosID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[issuedToken2]
ADD CONSTRAINT [DF_issuedToken_GenerationDate]
DEFAULT (getdate()) FOR [GenerationDate]
GO
This is a stored procedure that you can use to get a token number. You can have 100 systems calling this simultaneously and they'll all get a different number:
CREATE PROC [dbo].[pGetToken]
@PosID INT
AS
BEGIN
SET NOCOUNT ON
insert into issuedToken2 (PosID)
VALUES(@PosID)
RETURN scope_identity()
END
GO
This is how you use it all: call the stored proc with a posid (in this example 7) to reserve the token number, then use it to get the barcode:
DECLARE @Token INT
EXEC @Token = pGetToken 7
SELECT @Token, [Barcode]
FROM issuedToken2
WHERE Token=@Token
Basically this works by using an identity - an incrementing number. I know your existing system doesn't work like this but you haven't explained why it needs to.
This assumes you have SQL Server 2012 (please clarify)
Not a complete answer but I can expand if you wish.
First create a sequence (just run this once):
create sequence CustomerCare
as integer
start with 51
increment by 1
minvalue 51
maxvalue 350
cycle;
now get the next sequence from it (run this as often as you like):
select next value for CustomerCare
This method can't hand out the same number to two different requests so you won't get duplicates. It will automatically wrap around when it gets to 350. You can create and use sequences for your other groupings. Much simpler than the other solution and 100% reliable.
Again I need to advise against creating magic number ranges for specific groups.
I got solution by changing my Stored Procedure that is accessing Next Number. Now logic is to lock Token table and getting next number. Now i am getting only unique numbers.
Thanks everyone for your kind responses.
