Re: How to generate unique invoice numbers for each day

Dne 16.1.2011 03:29, Jasen Betts napsal(a):
> On 2011-01-15, Andrus Moor <kobruleht2(at)hot(dot)ee> wrote:
>> Invoice numbers have format yymmddn
>>
>> where n is sequence number in day staring at 1 for every day.
>>
>> command
>>
>> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
>> '^[0-9]*'),'')::int),0)+1
>> FROM invoice
>> where date= ?invoicedate
>>
>> is used to get next free invoice number if new invoice is saved.
>>
>> If multiple invoices are saved concurrently from different processes, they
>> will probably get same number.
>>
>> How to get unique invoice number for some day in 8.1+ when multiple users
>> create new invoices ?
>
> use a sequence, reset the sequence each night.

This was already proposed but he can't do that as the users may issue
invoices for different days (not just for 'today'). Which means the user
may create an invoice for monday, then another one for tuesday and then
another one for monday again. A single sequence can't solve this.

So either he has to create a sequence for each day, but he'll get a lot
of sequences (and it's a bit messy), and there will be gaps (not sure if
this is acceptable).

Or he can use the solution proposed in the General Bits 130 (which is
basically the solution I've already proposed) and maintain gapless
sequences using a table.

Tomas