Ayman Ayman - 7 months ago 18
SQL Question

Getting next value from sequence sql

I am trying to get the next value from this filed

SU - 1 /2016


Query I used

SELECT RIGHT('000' + CAST(ISNULL(MAX(SUBSTRING(InvoiceNO,4, 1)), 0) + 1 AS VARCHAR(4)), 4)
from [dbo].[Invoice]


The query output is
0001
, it should be
0002
.

Answer

You substring this value SU - 1 /2016 from 4th position which gives you "-". So to get the 1 you need to start from 6th position which gives you expected output.

SELECT 
RIGHT('000' + CAST(ISNULL(MAX(SUBSTRING(InvoiceNO,6, 1)), 0) + 1 AS VARCHAR(4)), 4) 
from [dbo].[Invoice]