Mikelemuel - 3 months ago 13

SQL Question

I'm using

`@subno`

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
```