Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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