November 27, 2019 at 7:01 pm
OrderID is varchar(20) and store in order table like below.
201910120001, 201910120002, 201910130001, 201911040001, 201911200069...
How to code to reset OrderID from 0001 daily?
That is keep (CONVERT(VARCHAR(8), Getdate(), 112)) as first 8 characters
(CONVERT(VARCHAR(8), Getdate(), 112)) + '0001'
(CONVERT(VARCHAR(8), Getdate(), 112)) + '0002'
For example, today's OrderID from 201911270001, 201911270002... 201911270399...
but tomorrow will be 201911280001, 201911280002, ...
November 27, 2019 at 7:31 pm
Unless you are required to do this by law, it's more trouble than it's worth. If you absolutely have to do it, I would use a SEQUENCE and run a procedure every night to RESTART the sequence at the desired number.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 27, 2019 at 8:10 pm
Either by querying your own table or by storing the maximum sequence per day somewhere. Fyi, this is really not a good way to handle keys and is not recommended. Here's querying your own table:
drop table if exists #orders;
go
create table #orders(
OrderIDvarchar(20) primary key not null);
go
--insert #orders(OrderID) values('201911270001');
insert #orders(OrderID) values('201911260001');
with max_id_cte(max_order_id) as(
select
max(OrderID) max_order_id
from
#orders
where
cast(left(OrderID, 8) as date)=(convert(varchar(8), getdate(), 112)))
select
isnull(left(max_order_id, 8)+format(cast(right(max_order_id, 4) as int)+1, 'd4'), (convert(varchar(8), getdate(), 112))+'0001')
from
max_id_cte;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 27, 2019 at 8:12 pm
Considering that you only have 4 digits for your number, I am assuming that this is a low volume system.
In that light, you could use a watermark table to create and track numbers. In a very busy system, you could encounter hot-spotting in such a table. However, we use this system where we generate 1000s of values per second.
Create a tracking table for the Order Numbers
CREATE TABLE dbo.OrderID_Generator (
OrderDate date not null PRIMARY KEY CLUSTERED
, LastUsedID int not null
);
GO
Create a proc to generate the next Order Number
CREATE PROCEDURE dbo.GetNextOrderID
@OrderDate date
, @NextOrderID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Dynamically ensure that there is always a date to generate OrderIDs for
INSERT INTO dbo.OrderID_Generator ( OrderDate, LastUsedID )
SELECT @OrderDate, 0
WHERE NOT EXISTS (SELECT 1 FROM dbo.OrderID_Generator AS dst WHERE dst.OrderDate = @OrderDate)
-- Use QuirkyUpdate to get the next number for the given date
UPDATE dbo.OrderID_Generator
SET @NextOrderID = LastUsedID = LastUsedID + 1
FROM dbo.OrderID_Generator
WHERE OrderDate = @OrderDate;
END;
GO
Generate a single Order Number
DECLARE @OrderDate date = CAST(GETDATE() AS date);
DECLARE @NextOrderID int;
DECLARE @NextOrderNum varchar(12);
EXEC dbo.GetNextOrderID @OrderDate = @OrderDate
, @NextOrderID = @NextOrderID OUTPUT;
SELECT @NextOrderNum = (CONVERT(char(8), @OrderDate, 112))
+ RIGHT('0000' + CONVERT(varchar(4), @NextOrderID), 4)
SELECT @NextOrderNum AS NextOrderNum;
November 27, 2019 at 9:38 pm
Unless you are required to do this by law, it's more trouble than it's worth. If you absolutely have to do it, I would use a
SEQUENCEand run a procedure every night toRESTARTthe sequence at the desired number.
If I was really stuck with this method I would look into doing it this way.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 28, 2019 at 1:56 pm
This sounds like a good use for rownumber() over (partition by … order by …)
partition by the date - not sure how you would go about it. - but scdecade and drew are correct - this is not a way that will scale... it will get very bad very quickly
MVDBA
November 28, 2019 at 2:17 pm
This sounds like a good use for rownumber() over (partition by … order by …)
But how do you persist the numbers already used?
If you use a SEQ object, SQL tracks the used numbers and gives you the next one. However, in this case, the daily reset means that you can never use the SEQ to go back and insert for an earlier date, as the tracking has been deleted.
The method that I provided allows you to jump forward and back across dates, and still get a consistent numbering system. The tracking table is very lightweight as it only has a date and an int. If there is data for every day for 100 years, the table only grows to 36250 records
November 28, 2019 at 2:45 pm
hey, i'm only putting options out there - I don't have a full solution , just trying to help people think of solutions for themselves. 🙂
personally, i'd end up going for a persisted computed column and then end up getting it wrong.
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply