Rajiv Rajiv - 6 months ago 19
SQL Question

Generate custom order no based on date, 3 alphabet code and auto incremented serial no

I have the below table:

create table test
ID int not null AUTO_INCREMENT,
CreatedOn datetime not null DEFAULT CURRENT_TIMESTAMP,
OrderNo varchar(100) not null,
Primary Key (ID)

I wondering if I can have the order no something like this: DNB1605141

  • DNB - is the 3 letter code

  • 16 - is the current year - need to be picked up from createdon column or current timestamp

  • 05 - is the current month for that year - need to be picked up from createdon column or current timestamp

  • 14 - is the current date - need to be picked up from createdon column or current timestamp

  • 1 - is the first order

But with one condition, at the start of every month the serial no should reseed to 1.

For example: DNB1605011 (1st order for 1st May 2016), DNB1606011 (1st order for 1st June 2016)

I am writing a stored procedure to insert the values into the table. But I am not sure how to generate the Order No.

Please advice. Thanks


Instead of creating and monitoring via Scheduler event, I figured out to check this via date functions. Before the insert while creating the order no it will check if the DAYOFMONTH is 1, If Yes then it will reseed the Order No to 1 else it will pick up the max order no. I am storing the order no in a separate column and the prefix in a separate column.

For DNB1605141: DNB1605 in column A and 141 in column B

Here is the code for the order no generation:

CREATE PROCEDURE spCashDonation(IN fname varchar(50),IN lname varchar(50),IN telNo bigint, IN pmode tinyint,IN amt decimal(13,2), OUT rno varchar(20))
    Set @rmain := (select trim(concat('DNB', DATE_FORMAT(CURRENT_DATE(), '%y'), DATE_FORMAT(CURRENT_DATE(), '%m'))));

    IF ((trim(DATE_FORMAT(CURRENT_DATE(),'%m')) = 01) OR (trim(DATE_FORMAT(CURRENT_DATE(),'%m')) = 1)) THEN
        Set @rpart = 1;
    END IF;

    IF ((trim(DATE_FORMAT(CURRENT_DATE(),'%m')) != 01) OR (trim(DATE_FORMAT(CURRENT_DATE(),'%m')) != 1)) THEN
        Set @rpart := (select coalesce(max(ReceiptPart),0) from Donation) + 1;
    END IF;

 INSERT INTO Donation (ReceiptMain, ReceiptPart, firstName, lastName, telNo, payMode, Amount) VALUES (@rmain, @rpart, fname, lname, telNo, pmode, amt);

 Set @lid := (select max(LAST_INSERT_ID()));
        select concat(ReceiptMain,ReceiptPart) into rno from donation where id = @lid;