Mikelemuel Mikelemuel - 1 month ago 10
SQL Question

SQL Server stored procedure: finding the values of Odd and even

I'm using

@subno
as Input. And I had to find the odd and even numbers. 16 is not a fix and it can be any other number. My question is how to find the odd and even number of my input?

Lastly, subno includes ( . ) dot at any position. e.g 123456.789123 I need to find the odd and even number of "123456" and the odd and even number of "789123" the dot ( . ) is the separator.

Once you find the odd for the left side, sum them up together. Once you find the even number for left side sum it up as well and then add the total odd to the total even values. That goes the same for the right side eg "789123".

Please help me. this is my 2nd week of trying to find the solution. Once you find all the total values for each side, multiply them together. example "123456" - total value of odd and even * the "789123" total value of odd and even.

It is for the the check digit validation. Validating the subscriber number. after validating through the calculation it should match the calculated reference number to the valid check digit number. It's the business rule. Kind of algorithm

create procedure ProcedureName
(@subno VARCHAR(16), --Input the 16 subscriber number
@result INT OUT,
)
as
begin
IF(LEN(@subno) <> 16)
SET @result = 1 -- INVALID RESULT
ELSE
IF(@subno % 2 = 0)
SET @result = @subno - even numbers
ELSE
SET @result = @subno --odd numbers
end


Please see below my sample work

-- this is the sample

create procedure ProcedureName
(
@subno VARCHAR(20), --Subscriber no
@result INT OUT, --result is invalid for 1, valid for 0
@payamt int
)
as

DECLARE @WA VARCHAR(2)
DECLARE @Weights varchar(9)
DECLARE @I INT
DECLARE @WD INT
DECLARE @WP INT
DECLARE @A INT
DECLARE @B INT
DECLARE @R INT
DECLARE @WR INT

SET @WR = 0
SET @R = 0
SET @A = 0
SET @B = 0
SET @WP = 0
SET @I = 0

BEGIN

IF (LEN(@subNo) = 7) AND (SUBSTRING(@subno,1,1) = '2') OR (SUBSTRING(@subno,1,1) = '9')
BEGIN
SET @result = 0 --VALID
END
ELSE IF(LEN(@subno) = 8) AND (SUBSTRING(@subno,1,1) = '2') OR
(SUBSTRING(@subno,1,1) = '9')
BEGIN
SET @result = 0 --VALID
END
ELSE IF(LEN(@subno) = 9)
BEGIN
SET @WA = SUBSTRING(@subno,1,2)
IF(@WA = '65')
set @result = 1 -- INVALID
else
BEGIN
SET @Weights = '12121212'
SET @WA = SUBSTRING(@subno,9,1)
SET @WD = 0
SET @I = 1
WHILE @I<9
BEGIN
SET @WP = cast(SUBSTRING(@Weights, @I,1)as int) * cast(SUBSTRING(@subno, @I, 1) as int)
IF(@WP > 9)
BEGIN
SET @A = SUBSTRING(CAST(@WP AS VARCHAR),1,1)
SET @B = SUBSTRING(CAST(@WP AS VARCHAR),2,1)
SET @WP = CAST(@A AS INT) + CAST(@B AS INT)
END

SET @WD = @WP + @WD
SET @I = @I + 1
END

SET @R = @WD % 10

IF(@R <> 0)
SET @WR = 10 - @R
ELSE
SET @WR = @R

IF(@WR <> CAST(@WA AS INT))
BEGIN
SET @result = 1 -- INVALID
END
ELSE
BEGIN
SET @result = 0 -- VALID
END
END
END
ELSE IF (LEN(@subno) = 10)
BEGIN
SET @I =1
SET @WD = 0
SET @Weights = '121212121'
SET @WA = SUBSTRING(@subno,10,1)
WHILE(@I < 10)
BEGIN
SET @WP = CAST(SUBSTRING(@Weights, @I, 1)AS INT) * CAST(SUBSTRING(@subno, @I, 1) AS INT)
IF(@WP > 9)
BEGIN
SET @A = SUBSTRING(CAST(@WP AS VARCHAR),1,1)
SET @B = SUBSTRING(CAST(@WP AS VARCHAR),2,1)
SET @WP = CAST(@A AS INT) + CAST(@B AS INT)
END

SET @WD = @WP + @WD
SET @I = @I + 1
END

SET @R = @WD % 10

IF(@R <> 0)
SET @WR = 10 - @R
ELSE
SET @WR = @R

IF (@WR<> @WA)
BEGIN
SET @result = 1 -- INVALID
END
ELSE
BEGIN
SET @result = 0 -- VALID
END
END
ELSE
SET @result = 1 -- INVALID
END

Answer

Split the values which u get . Then iterate iver each side and add them. Please see the sample below.

declare @v varchar (16) , @num1 varchar(20) , @num2 varchar(20)
set @v = '1234567.78906656'

select  @num1 = substring(@v,0,charindex('.',@v))

select @num2 = substring(@v,charindex('.',@v)+1,len(@v))
--select @num1 = convert(int, substring(@v,0,charindex('.',@v)))
--select @num2 =  substring(@v,charindex('.',@v)+1,len(@v))
declare @index int = 1 ,@len INT , @char CHAR
declare @TotalOddL int  = 0
declare @TotalEvenL int  = 0
DECLARE @FullTotL INT = 0
declare @TotalOddR int  = 0
declare @TotalEvenR int  = 0
DECLARE @FullTotR INT = 0
DECLARE @TEMP INT
set @len= LEN(@num1)



WHILE @index <= @len
BEGIN
set @char = SUBSTRING(@num1, @index, 1)
SET @TEMP = cast(@char as int)
IF(@TEMP % 2 = 0)
   SET  @TotalEvenL = @TotalEvenL + @char
     else
   SET  @TotalOddL = @TotalOddL + @char

   SET @FullTotL = @TotalEvenL + @TotalOddL 


SET @index= @index+ 1
END

Select 'LeftSide total' , @FullTotL
Select 'Left Side odd' , @TotalOddL
Select 'Left Side Even' , @TotalEvenL

SET @index = 1
set @len= LEN(@num2)
WHILE @index <= @len
BEGIN
set @char = SUBSTRING(@num2, @index, 1)
SET @TEMP = cast(@char as int)
IF(@TEMP % 2 = 0)
   SET  @TotalEvenR= @TotalEvenR + @char
     else
   SET  @TotalOddR = @TotalOddR + @char

   SET @FullTotR = @TotalEvenR + @TotalOddR 

SET @index= @index+ 1
END


select 'TotalRSide' , @FullTotR
select 'RsideOdd' , @TotalOddR
select 'RSideEven' , @TotalEvenR

select 'Multiplied value' , @FullTotR * @FullTotL
Comments