# SQL Server return Max(value) based on criteria

Hi,

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.
Examples:
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?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft SQL Server Data DudeCommented:
>In a nVarChar database field a number is stored.
There's your first problem, as this is going to introduce a lot of non-numeric data entry, such as 25 or 6 to 4, 4 1/2, n/a, or 'banana' that will need to be worked out before any numeric calculations can take place.

>Odd numbers are stored in following ranges:
>Even numbers are stored in following ranges:
Just for kicks and giggles, please explain how these logic was created.
Microsoft SQL Server Data DudeCommented:
There's probably a more elegant way to pull this off, but here you go.
Copy-paste the below code blocks into your SSMS and execute, then modify to fit your needs

Create a temp table just for testing
``````IF EXISTS (SELECT name FROM sys.tables WHERE name='tmp')
DROP TABLE tmp
GO

CREATE TABLE tmp (MyField nvarchar(100))
``````

Execute this function
``````CREATE FUNCTION [dbo].[fn_get_new_number] ()
RETURNS varchar(100)
AS
begin

Declare @return varchar(100)

-- Get the max of MyField, store as int
Declare @Max_MyField int
SELECT @Max_MyField = MAX(CAST(MyField as int)) FROM tmp WHERE ISNUMERIC(MyField) = 1

-- Add 1 or 1001 depending on value
SELECT @return = CASE RIGHT(LEFT(CAST(@Max_MyField as varchar(100)), 5), 2)
WHEN '99' THEN CAST(@Max_MyField + 1001 as varchar(100)) ELSE CAST(@Max_MyField + 1 as varchar(100)) END
FROM tmp
WHERE ISNUMERIC(MyField) = 1

RETURN @return
end
``````
Then here's some testing script
``````-- TESTING, returns 12022 + 1
SELECT dbo.fn_get_new_number()

-- TESTING 17304, returns 17305
INSERT INTO tmp (myField) VALUES('17304')
SELECT dbo.fn_get_new_number()

-- TESTING 19999, returns 21000
INSERT INTO tmp (myField) VALUES('19999')
SELECT dbo.fn_get_new_number()
``````
DeveloperAuthor Commented:
The only thing is that I need to get either a max(value) in the range of even numbers or for odd numbers.
In the database the numbers for both groups are stored in one field.

So getting the max(value) for the odd numbers group would only look for a max(value) with the same numberrange.

MyField values in database:
11000
11001
12000
11002
12003
11003
12006

Result for odd group: 11004 (11003+1)
Result for even group: 12007 (12006+1)
DeveloperAuthor Commented:
@Jim Horn:
In answer to your first post on how this logic was created.
A customer thought of some logic to make a distinction between two groups of customers. In those days it probably looked as the right thing to do, but now that they have grown bigger, the chosen logic makes life more complicated :-)
Maybe in the earlier days they only had a numeric field at their disposal?
MSSQL Senior EngineerCommented:
Try this function:
``````CREATE FUNCTION [dbo].[fn_get_NextValue] ()
RETURNS VARCHAR(5)
AS
BEGIN

DECLARE @NextValue VARCHAR(5)
DECLARE @IntValue INT

-- Get the MAX value
SELECT @NextValue = MAX(MyField) FROM MyTableName

SET @IntValue = CAST(@NextValue AS INT)

IF RIGHT(@NextValue,3)='999'	-- If will turn to next thousand
@IntValue = @IntValue + 1000	-- add one thousand before

@IntValue = @IntValue + 1	-- Now just increase the value by 1

@NextValue = CAST(@IntValue AS VARCHAR)

RETURN @NextValue
END
``````
DeveloperAuthor Commented:
MSSQL Senior EngineerCommented:
I read that but I could misunderstood it. You said: "In the database the numbers for both groups are stored in one field."
So, I assumed that field has only sequential numbers so you just need to grab the last value and increment by one.
Or do you to increment simultaneously the maximum values for odd and even numbers?
DeveloperAuthor Commented:
Yes, I  need to increment simultaneously the maximum values for odd and even numbers.

Basically I either need to know the next ODD or the next EVEN number.
So the function must look for the max(value) only with the specific range of number (ODD or EVEN).
Problem resolverCommented:
Try

select min(myfield) from tab1 where myfield=@val

suppose that @val is the value to which you're finding the next value
Information Technology SpecialistCommented:
with cte as
(select case when floor(cast(myfield as int)/1000) % 2 = 0 then 'even' else 'odd' end as grp,
cast(myfield as int) + 1 as num
from table)
select grp,
case when max(num) % 1000 = 0 then max(num) + 1000 else max(num) end as nextnum
from cte
group by grp
order by nextnum;

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Microsoft SQL Server Data DudeCommented:
>Yes, I  need to increment simultaneously the maximum values for odd and even numbers.
Explain how you wish to see this.
One function that returns two values, next odd number and next even number?
One function that returns the next number based on the max value in the table?
One function that passes a parameter of a number, and returns the next number?
One SP that inserts into the table the next even and odd number?

This is not clear to us.
Information Technology SpecialistCommented:
With your sample data, my query should return odd, 11004 and even, 12007. If there were a record like 12999, it should return odd, 11004 and even, 14000
DeveloperAuthor Commented:
One function that returns two values, next odd number and next even number?
No only one value is required, either for EVEN or ODD number range.
One function that returns the next number based on the max value in the table?
Yes, but it must look only inside the range ODD or EVEN dependend which next value is required.
One function that passes a parameter of a number, and returns the next number?
Yes, parameter would be ODD or EVEN.
One SP that inserts into the table the next even and odd number?
No this is not required. I will get the number within a Delphi application an process it accordingly.
DeveloperAuthor Commented:
This works perfectly.
Senior DBACommented:
Something like this.  For best efficiency on scalar functions, use just a single RETURN statement in the function whenever possible.

CREATE FUNCTION Get_Next_MyField_Value (
@OddOrEven char(4)
)
RETURNS nvarchar(5)
AS
BEGIN
RETURN (
SELECT MyField_Max + 1 + CASE WHEN MyField_Max % 1000 = 999 THEN 1000 ELSE 0 END AS MyField_Next_Value
FROM (
SELECT CAST(MAX(MyField) AS int) AS MyField_Max
FROM table_name tn
CROSS APPLY (
SELECT CAST(LEFT(MyField, 2) AS tinyint) AS MyField_Range
) AS assign_alias_names
WHERE
MyField LIKE '[0-9][0-9][0-9][0-9][0-9]' AND
((@OddOrEven = 'Odd' AND MyField_Range % 2 = 1) OR
(@OddOrEven = 'Even' AND MyField_Range % 2 = 0))
) AS derived
)
END
GO --end of function
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.