How to reset orderID daily

  • 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, ...

  • 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

  • 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

  • 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;

     

  • drew.allen wrote:

    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.

    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

  • 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

  • MVDBA (Mike Vessey) wrote:

    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

     

  • 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