Invoice numbering system

I am creating an invoicing structure in our database. Our system allows
multiple companies to be setup within the one database. Each company should
have seperate invoice number sequences. E.g.
Company One's last invoice number was 10000
Company Two generates a new invoice. They should be given either 10000 or a
number in a completely different range. But they should not be given 10001
(in company one's sequence).
Has anyone any suggestions on how to create a system for generating invoice
numbers. Two users should never be given the same invoice number (within
the same company). There should never be any gaps in invoice numbers (e.g.
Company One next invoice number after 10000 must be 10001 and then 10002
etc).
I need a guaranteed system that will generate an invoice number using the
rules above.
Thanks,
ChrisIf I was to generate my next number using something such as this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
BY InvoiceNo DESC
This approach seems to work, except for when I generate my first invoice
because the select clause returns nothing. So I tried this...
INSERT INTO Invoices (InvoiceNo)
SELECT TOP 1 InvoiceNo FROM (
SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialNo
= 5
UNION
SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
This seems to work. Does this sound a good approach for generating invoice
numbers that are unique to a company? And does this guarantee two users
cannot be given the same number?
Thanks,
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I am creating an invoicing structure in our database. Our system allows
>multiple companies to be setup within the one database. Each company
>should have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
> a number in a completely different range. But they should not be given
> 10001 (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating
> invoice numbers. Two users should never be given the same invoice number
> (within the same company). There should never be any gaps in invoice
> numbers (e.g. Company One next invoice number after 10000 must be 10001
> and then 10002 etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>|||put it on separate tables
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Chris" wrote:

> I am creating an invoicing structure in our database. Our system allows
> multiple companies to be setup within the one database. Each company shou
ld
> have seperate invoice number sequences. E.g.
> Company One's last invoice number was 10000
> Company Two generates a new invoice. They should be given either 10000 or
a
> number in a completely different range. But they should not be given 1000
1
> (in company one's sequence).
>
> Has anyone any suggestions on how to create a system for generating invoic
e
> numbers. Two users should never be given the same invoice number (within
> the same company). There should never be any gaps in invoice numbers (e.g
.
> Company One next invoice number after 10000 must be 10001 and then 10002
> etc).
> I need a guaranteed system that will generate an invoice number using the
> rules above.
>
> Thanks,
> Chris
>
>|||Chris ,
why dont u try describing the column as identity using newid function to
give to unque number everytime
"Chris" wrote:

> If I was to generate my next number using something such as this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDE
R
> BY InvoiceNo DESC
>
> This approach seems to work, except for when I generate my first invoice
> because the select clause returns nothing. So I tried this...
> INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerial
No
> = 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
> This seems to work. Does this sound a good approach for generating invoic
e
> numbers that are unique to a company? And does this guarantee two users
> cannot be given the same number?
> Thanks,
> Chris
>
> "Chris" <cw@.community.nospam> wrote in message
> news:%23QF7DOqvFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
>|||On Wed, 21 Sep 2005 13:29:44 +0100, Chris wrote:

>If I was to generate my next number using something such as this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo + 1 FROM Invoices WHERE CompanySerialNo = 5 ORDER
>BY InvoiceNo DESC
>
>This approach seems to work, except for when I generate my first invoice
>because the select clause returns nothing. So I tried this...
>INSERT INTO Invoices (InvoiceNo)
> SELECT TOP 1 InvoiceNo FROM (
> SELECT InvoiceNo + 1 As InvoiceNo FROM Invoices WHERE CompanySerialN
o
>= 5
> UNION
> SELECT 1 AS InvoiceNo) SubQuery ORDER BY InvoiceNo DESC
>
>This seems to work. Does this sound a good approach for generating invoice
>numbers that are unique to a company? And does this guarantee two users
>cannot be given the same number?
Hi Chris,
This one is easier, and probably quicker as well:
INSERT INTO Invoices (InvoiceNo)
SELECT COALESCE(MAX(InvoiceNo),0) + 1
FROM Invoices
WHERE CompanySerialNo = 5
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
That's more readable!
Chris

> Hi Chris,
> This one is easier, and probably quicker as well:
> INSERT INTO Invoices (InvoiceNo)
> SELECT COALESCE(MAX(InvoiceNo),0) + 1
> FROM Invoices
> WHERE CompanySerialNo = 5
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)