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?
Stef MerlijnDeveloperAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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)) 

Open in new window


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

Open in new window

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()

Open in new window

0
Stef MerlijnDeveloperAuthor 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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Stef MerlijnDeveloperAuthor 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?
0
Vitor MontalvãoMSSQL 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

Open in new window

0
Stef MerlijnDeveloperAuthor Commented:
0
Vitor MontalvãoMSSQL 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?
0
Stef MerlijnDeveloperAuthor 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).
0
HuaMin ChenProblem resolverCommented:
Try

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

suppose that @val is the value to which you're finding the next value
0
awking00Commented:
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;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
awking00Commented:
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
0
Stef MerlijnDeveloperAuthor 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.
0
Stef MerlijnDeveloperAuthor Commented:
This works perfectly.
0
Scott PletcherSenior 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
0
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.

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.