convert excel function to sql command 2

[dodge20]

Can anyone help convert this function to a sql statement.

MOD(10-(SUM(CEILING(MOD(MID(@checkdigit,{1,2,3,4,5,6,7,8,9},1)*{2,1,2,1,2,1,2,1,2},9.5),1))),10)

Dodge20

 

[NoCoolHandle]

Where do you see the data comming from?
If tables, can you give a quick example of the structure and data?

e.g.

table1
col1 col2 col3
1 3 2
3 3 2
4 5 12

and is check digit a parameter that the user would enter?
is the array a series of rows in a table, or is it a string values stored in a single column?

a quick definition of the equiv methods in sql...

sum = sum
mod = %
ceiling=ceiling
mid = substring

HTH

Rob

 

[SkipVought]

Hi,

Here's what the Excel formula is doing...
[tt]
For each of the first 9 digits of @checkdigit, ODD positions are multiplied by 2, EVEN positions by 1.

The modulus of [each array element result] and 9.5 is rounded UP and SUMMED

Then modulus of [10 minus the sum] and 10
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dodge20]

Thanks for your help. I was able to create a function that does this.


Dodge20