CODEWITHSUNDEEP
c#sql-serversql-server-2014
Taimur Adil
Taimur Adil

Reputation: 77

YYMM#### how to generate invoice number

i have to generate invoice number like YYMM#### YY and MM are easy and we can get them using today date but how to add custom 4 digit numbers starts from 001 and end 999 i am using sql for storing invoice numbers

Upvotes: 0

Views: 3861

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1503409

If you only have at most 999 invoices per month, you probably don't need to worry two much about the inefficiencies involved in two invoices in quick succession, so each you need to generate an invoice:

  • Work out the prefix to use (be careful with time zones here...)
  • Find the highest invoice ID with a query to find all invoice IDs starting with your prefix (or between the min and max possible values if your using a numeric type), ordering by ID (descending), taking just the first one 1
  • Parse this and add one to generate the new ID
  • Try to insert a new invoice with the given ID
  • If you get a constraint violation (duplicate ID), go through the process again

If you had to handle lots of invoices, potentially generated from lots of different clients, and didn't need contiguous invoice IDs, you could use a lo/hi algorithm with each client effectively "reserving" IDs. That sounds like overkill for this situation though.

Oh, and you should work out what you want to happen if there are more than 999 invoices in one single month...

1 You could potentially avoid the filtering here, and assume that everything else will follow the same convention, but personally I'd filter

Upvotes: 2

M.Ali
M.Ali

Reputation: 69574

If you want to do this in SQL Server side , you would need to create a SEQUENCE object in sql server and you can do something like ..

SEQUENCE

CREATE SEQUENCE dbo.GetInvoiceNumber
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1 
    MAXVALUE 999
    CYCLE
    CACHE 10
;

SQL Server Call

SELECT CONVERT(VARCHAR(4), GETDATE(), 12) 
       + RIGHT('0000' 
       + CAST( NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)),4)

Result

The query will return values like 

15080001
15080002
15080003
15080004
.......

Note

Are you sure your customer will never produce more than 999 Invoices in one month, I think the number should be fairly high just in case.

Upvotes: 2

Related Questions