How can I create a unique invoice number?

jewel 901

I want to use a unique invoice number in my work. I am using SQL Server database.

I want an invoice number to be generated which will have current year first with autoincrement number. When the year changes to a new year, the invoice number will start over.
For example, 2023/00001
2023/00002
When the new year comes the invoice number will be-
2024/00001
2024/00002
This will continue.
It would be helpful if any experienced help. thanks in advance

 public class tbl_Sell
    {
        [Key]
        public int SelliD { get; set; }
        public String InvoiceNO { get; set; }
        public int productID { get; set; }
        public int Qty { get; set; }
       
    }

AgaveJoe 1,500 Reputation points

2023-10-27T16:18:23.44+00:00

You solved the problem now it just a matter of writing the logic.

For example, 2023/00001

You need two columns. One to store the year (int) and the other to store an a sequence (int).

When the new year comes the invoice number will be-

Fetch the MAX(yearColumn) and compare the value to the current year. If the current year does not match the MAX(yearColumn) then set the sequence field to 1.

If the MAX(yearColumn) equals the current year then get the max sequence number and add one.

When displaying the invoice, simply pad the sequence column with zeros and concatenate to with the year column.

With the amount of experience you have on these forums you should be able to figure the logic out on your own. If you run into trouble then post the code you're written. Explain how you expect the code to function and how the code actually functions.

I would write a stored procedure.

MAX (Transact-SQL)

CREATE SEQUENCE (Transact-SQL)

CONCAT (Transact-SQL)

https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server

Accepted answer

Bruce (SqlWork.com) 65,316

if you use a table with a max value, be sure to proper locking to prevent race conditions or use a single statement:

create table NextOrderNumber
(
    Year int not null,
    Number int not null
)
go

declare @year int = 2023;
declare @number int = 1;

merge NextOrderNumber as n
using (select @year as year) as y 
   on n.Year = y.Year 
when matched then
    update set 
        Number = Number + 1,
        @number = Number + 1 
when not matched then
    insert (Year, Number)
    values (y.year, @number);

select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4);

jewel 901

Thanks for the reply. But when I try to get this query as json data I am getting error. I just don't understand how to use it in ASP.NET Core.

This is error:

InvalidOperationException: The required column 'Year' was not present in the results of a 'FromSql' operation.

         //Controller   
  public JsonResult GetInvoiceNO()
        {  
            string query = $"declare @year int = 2025; declare @number int = 1;  merge NextOrderNumber as n using (select @year as year) as y     on n.Year = y.Year  when matched then     update set          Number = Number + 1,         @number = Number + 1  when not matched then     insert (Year, Number)     values (y.year, @number);  select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4); ";
            var List = _context.NextOrderNumber.FromSqlRaw(query);
            return Json(List);

        }

Share via

How can I create a unique invoice number?

0 additional answers

Your answer