We help IT Professionals succeed at work.

Pulling Highest Invoice Number from database with invoice format G-111

463 Views
Last Modified: 2013-11-19
I have an asp website that is used for entering invoices.  For the invoice number, I need to pull the highest invoice number from the database so that I can add 1 to the number part and use it for the newly entered invoice.  The invoice number is in the format: G-111.  How can I pull the highest invoice number from the SQL database so that I increment the invoice numbers correctly?

Thanks for all of your help.
Comment
Watch Question

Doug BishopDatabase Developer

Commented:
SELECT NextNumber = TOP 1 SUBSTRING(InvoiceNumber, 1, 2) + CAST(CAST(SUBSTRING(InvoiceNumber, 3, LEN(InvoiceNumber) - 2) AS INT) AS VARCHAR) FROM tblInvoices ORDER BY InvoiceNumber
Doug BishopDatabase Developer

Commented:
Sorry, change to:
SELECT NextNumber = TOP 1 SUBSTRING(InvoiceNumber, 1, 2) + CAST(CAST(SUBSTRING(InvoiceNumber, 3, LEN(InvoiceNumber) - 2) AS INT) + 1 AS VARCHAR) FROM tblInvoices ORDER BY InvoiceNumber
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT MAX(InvoiceNumber)
from urTable

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
If all the invoices starts with a 'G-', i would appreciate, if you can change the db design.You can add a 'G-' at the front end and the store only an integer value, which can be Identity, at the db

Commented:
You can do like this:

declare @invoiceNumber varchar(12)
select @InvoiceNumber = 'G-' + cast(max(cast(substring(InvoiceNumber,3,99) as int)) +1 as varchar(10))


However, ather than "pulling" the max number into your ASP page, just derive the next number on the insert.  

Insert into YourTable (col1, InvoiceNumber, col3)
   values (val1
         ,'G-' + cast((select max(cast(substring(InvoiceNumber,3,99) as int)) + 1 as varchar(10))
         , val3)
   
         
     
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT 'G-'+ MAX(CAST(RIGHT(InvoiceNumber,LEN(InvoiceNumber) - CHARINDEX('-', InvoiceNumber) )AS INT ))  
FROM Invoices
WHERE invoiceNumber Like 'G-%'

Commented:
And I meant:

Insert into YourTable (col1, InvoiceNumber, col3)
   values (val1
         ,'G-' + cast((select max(cast(substring(InvoiceNumber,3,99) as int))  from YourTable ) + 1 as varchar(10))
         , val3)

Commented:
BTW, I agree with Aneesh.  If ALL your invoices start with "G-", there is no point in storing that prefix in the database, really.  It's much easier to increment an integer.  If you anticipate something besides a "G-" prefix, then none of the techniques presented will work when you get there.  I agree, store the numeric part of the invoice as an integer.  You do not need to store the prefix, but if you are so inclined, store it in a separate field.

Commented:
Try this
 
select max(cast(substring(InvoiceNumber, charindex('-', InvoiceNumber)+ 1, len(InvoiceNumber))as int)) from <table name> where InvoiceNumber like 'G%'
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.