YYMM#### how to generate invoice number

Question

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

what SQL RDBMS are you using ? SQL Server ,MySQL or Oracle etc ??? — M.Ali, Aug 29 '15 at 13:55
Are you interested only in the conversion to a string or how to retrieve the next number to use from the database. — Steve, Aug 29 '15 at 13:57
Do you have many users that concurrently add invoices to your database table? — Steve, Aug 29 '15 at 13:59
for(int i = 1; i <= 999; i++) { string iNo = string.Format("{0}#{1}",DateTime.Now.ToString("yyMM"), i.ToString("#000")); } — jdweng, Aug 29 '15 at 14:05
4 digits or 3 digits? 4 digits go from 0001 to 9999 so you have 9999 invoices per month/year — Steve, Aug 29 '15 at 14:18

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

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 ¹
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...

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

edited May 23 '17 at 11:44

Community

1
1

answered Aug 29 '15 at 14:01

Jon Skeet

1,421,763
867
9,128
9,194

using MAX() aggregate function ? – Taimur Adil Aug 29 '15 at 14:08
@TaimurAdil: Could do. Or order by and limit. It's not clear whether you're representing these IDs as text or numbers, btw. – Jon Skeet Aug 29 '15 at 14:09
these id's are limited so i m using int for that – Taimur Adil Aug 29 '15 at 14:10
@JonSkeet using MAX function maybe ok now but going forward the query will become more and more expensive as the size of the table grows, in my humble opinion a SEQUENCE object will be a better option for this. – M.Ali Aug 29 '15 at 14:16
right because after 1 year DB could be in MB's or GB's @M.Ali – Taimur Adil Aug 29 '15 at 14:21
@M.Ali: I'm assuming this would be indexed. With the filter in place, any reasonable database would cope with absolutely no problems. – Jon Skeet Aug 29 '15 at 14:35

score 2 · Answer 2 · answered Aug 29 '15 at 14:08

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

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.

Cache should probably be 1 to reduce numbers wasted by gaps after service restart. — Martin Smith, Aug 29 '15 at 14:44
It should be noted that in case of a rollback there will be gaps in the sequence which is sometimes not acceptable for an invoice number — Bill Tür stands with Ukraine, Aug 29 '15 at 19:36

YYMM#### how to generate invoice number

2 Answers2

SEQUENCE

SQL Server Call

Result