May 5, 2016 at 2:11 pm
Hi All,
Hope everybody is fine. I have to perform a task as stated below:
Please note that the values under consideration contains only numeric values such as 234234,234089,295380,111111 etc. But field type is Varchar.
Task is to mark values into Platinum,Gold,Silver,Regular on the basis of following criteria:
1. If the digits of a values are same i.e. (111111) or digits are in a natural sequence e.g. (123456) than mark them Platinum
2. If the any of four digits are in a natural sequence e.g. (123469) than mark them Gold.
3. If the any of three digits are in natural sequence e.g. (123957) than mark them Silver.
Rest of pair will be marked Regular. Further, the sequence might be inverse e.g. 321,4321 etc
I am unable to get any clue that what term could be assigned to such operation and how it could be done in MS SQL SERVER (2000/2008R2)
Regards,
Rehan
May 5, 2016 at 2:38 pm
I need to get out of the office, but this might give you an idea for a function.
--234234,234089,295380,111111
DECLARE @number varchar(10) = 123469;
DECLARE @CountConsecutive int = 1,
@MaxConsecutive int = 1,
@CountEqual int = 1;
SELECT @CountConsecutive = CASE WHEN SUBSTRING( @number, n, 1) = SUBSTRING( @number, n + 1, 1) - 1
THEN @CountConsecutive + 1
ELSE 1 END
,@MaxConsecutive = CASE WHEN @CountConsecutive > @MaxConsecutive
THEN 0 + @CountConsecutive ELSE 0 + @MaxConsecutive END
,@CountEqual = CASE WHEN SUBSTRING( @number, n, 1) = SUBSTRING( @number, n + 1, 1)
THEN @CountEqual + 1
ELSE 1 END
FROM (SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 )x(n)
WHERE n <= LEN(@number) - 1;
SELECT CASE WHEN @CountEqual = LEN(@number) OR @MaxConsecutive = LEN(@number) THEN 'Platinum'
WHEN @MaxConsecutive >= 4 THEN 'Gold'
WHEN @MaxConsecutive = 3 THEN 'Silver'
ELSE 'Regular' END;
May 5, 2016 at 7:50 pm
This solution assumes that your sequences are always going to be 6 characters long...
Taking Luis' brilliant solution and turning it into a scalar UDF like this:
CREATE FUNCTION dbo.SeqRank (@number CHAR(6))
RETURNS varchar(8) AS
BEGIN
DECLARE @CountConsecutive int = 1,
@MaxConsecutive int = 1,
@CountEqual int = 1;
SELECT @CountConsecutive = CASE WHEN SUBSTRING( @number, n, 1) = SUBSTRING( @number, n + 1, 1) - 1
THEN @CountConsecutive + 1
ELSE 1 END
,@MaxConsecutive = CASE WHEN @CountConsecutive > @MaxConsecutive
THEN 0 + @CountConsecutive ELSE 0 + @MaxConsecutive END
,@CountEqual = CASE WHEN SUBSTRING( @number, n, 1) = SUBSTRING( @number, n + 1, 1)
THEN @CountEqual + 1
ELSE 1 END
FROM (SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 )x(n)
WHERE n <= LEN(@number) - 1;
RETURN
(
CASE WHEN @CountEqual = LEN(@number) OR @MaxConsecutive = LEN(@number) THEN 'Platinum'
WHEN @MaxConsecutive >= 4 THEN 'Gold'
WHEN @MaxConsecutive = 3 THEN 'Silver'
ELSE 'Regular' END
)
END;
GO
You can use getnumsAB[/url] to split the string and evaluate it like this:
DECLARE @string varchar(1000) = '234234,234089,295380,111111';
SELECT
Seq = SUBSTRING(@string,n1,6),
SeqRank = dbo.SeqRank(SUBSTRING(@string,n1,6))
FROM dbo.GetNumsAB(1,CAST(LEN(@string) AS bigint),7,1);
Edit: little typo.
-- Itzik Ben-Gan 2001
May 6, 2016 at 3:28 am
Thanks Luis, it worked. You provided the the solution as well as introduced me to the approach for doing this.
Thanks Alan Burstein, seems there some typo but i will fix it for UDF.
🙂
May 6, 2016 at 6:43 am
You're welcome. I hope you understand how this works, and if you don't ask any questions you might have.
I tried to keep it 2000 compatible, but a different solution might be available with ranking functions and other functionality introduced in more recent versions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply