CODEWITHSUNDEEP
mysqlgroupingauto-incrementstored-functions
Lasse Staalung
Lasse Staalung

Reputation: 115

How to generate unique grouped autoincrement document id's?

I need to make some tables which are able to auto-increment unique document names for each month of the year based on the date of each documnet type. I think it will be a good idea to make it as a generated stored column in a MySql table. Thailand uses the Buddhist calendar where you normally add 543 years to the Christian calendar (2024 becomes 2567) in this case I add 43 years as I only need the last digits of the year. I am then missing the last serial number to number which I cannot find a solution to after intense searching on the internet. I would think it should be found under grouping. In this case, I am working with the invoices.

My table:

CREATE TABLE `tbl_0_invoices` (
  `invoice_date` date NOT NULL,
  `invoice_no` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci GENERATED ALWAYS AS (concat(_utf8mb4'INV',(date_format(`invoice_date`,_utf8mb4'%y') + 43),date_format(`invoice_date`,_utf8mb4'%m'))) STORED;
  
 ALTER TABLE `tbl_0_invoices`
  ADD PRIMARY KEY (`invoice_no`);

I have not been able to find any documentation on the issue, even this kind of document identification is quite normal in Thailand.

I expect this output.

invoice_date invoice_no
21/8/2567 INV6708-1
13/9/2567 INV6709-1
13/9/2567 INV6709-2
13/9/2567 INV6709-3
13/9/2567 INV6709-4
18/9/2567 INV6709-5
25/10/2567 INV6710-1
25/10/2567 INV6710-2
25/10/2567 INV6710-3
25/10/2567 INV6710-4
24/1/2568 INV6801-1

Upvotes: 0

Views: 61

Answers (1)

ArtBindu
ArtBindu

Reputation: 2016

Use DATE_FORMAT(date, "%y%m") to get the numeric year & month in format: YYMM.

Use row_number() OVER (PARTITION BY date) to get the increment value w.r.t YYMM value.

So, this is simple string joining query.
and MySQL query should be:

select 
    DATE_FORMAT(cast(invoice_date as date), '%d/%m/%Y') as invoice_date,
    CONCAT("INV", 
        DATE_FORMAT(cast(invoice_date as date), "%y%m"),
        "-",
        row_number() OVER (PARTITION BY DATE_FORMAT(cast(invoice_date as date), "%y%m"))
    ) as invoice_no
from mySQLTable;

Check Result: db<>fiddle

Result as follows:

enter image description here

Upvotes: 1

Related Questions