In a nVarChar databasefield a number is stored.
The first two characters of this number either is odd or even. All odd numbers belong to one group and all even numbers to another group. All numbers have 5 digits.
Odd numbers are stored in following ranges:
11000 to 11999, 13000 to 13999, 15000 to 15999, 17000 to 17999 and 19000 to 19999, etc.
Even numbers are stored in following ranges:
12000 to 12999, 14000 to 14999, 16000 to 16999, 18000 to 18999 and 20000 to 20999, etc.
I need to create an Function, Stored procedure or SQL that will return the next number that will fall within the above mentioned ranges.
If Max(MyField) for even numbers is 12111 -> return is: 12112
If Max(MyField) for even numbers is 12999 -> return is: 14000
If Max(MyField) for odd numbers is 11304 -> return is: 11305
If Max(MyField) for odd numbers is 13999 -> return is: 15000
Does anybody have a flexible solution for this?