• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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

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.
0
sigkappu
Asked:
sigkappu
  • 4
  • 3
  • 2
  • +1
1 Solution
 
dbbishopCommented:
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
0
 
dbbishopCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT MAX(InvoiceNumber)
from urTable

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
dqmqCommented:
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)
   
         
     
0
 
dqmqCommented:
Oops, meant:
declare @invoiceNumber varchar(12)
select @InvoiceNumber = 'G-' + cast(max(cast(substring(InvoiceNumber,3,99) as int)) +1 as varchar(10)) from YourTable
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT 'G-'+ MAX(CAST(RIGHT(InvoiceNumber,LEN(InvoiceNumber) - CHARINDEX('-', InvoiceNumber) )AS INT ))  
FROM Invoices
WHERE invoiceNumber Like 'G-%'
0
 
dqmqCommented:
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)
0
 
dqmqCommented:
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.
0
 
assystCommented:
Try this
 
select max(cast(substring(InvoiceNumber, charindex('-', InvoiceNumber)+ 1, len(InvoiceNumber))as int)) from <table name> where InvoiceNumber like 'G%'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now