YYMM#### how to generate invoice number

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