| Author |
 Topic  |
|
|
borisowen
Starting Member
1 Posts |
 Posted - 06/21/2012 : 02:31:26
|
I have a excel formula i need to convert to an SQL query.
The formula is
=10-MOD((((MID(A4,2,1))+(MID(A4,4,1))+(MID(A4,6,1))+(MID(A4,8,1))+(MID(A4,10,1))+(MID(A4,12,1)))*3)+((MID(A4,1,1))+(MID(A4,3,1))+(MID(A4,5,1))+(MID(A4,7,1))+(MID(A4,9,1))+(MID(A4,11,1))),10))
The database is SP_VALIDATION_Data
I need to set R01_Check to equal the answer to the excel formula
In the query A4=MembershipID
So i assume it would start
UPDATE SP_VALIDATION_Data
SET R01_Check =
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/21/2012 : 03:52:54
|
mid will translate to substring. mid(A4,4,1) will be substring(col,4,1)
mod is % i.e. mod(exp,10) will be exp % 10
You don't say if yoou have a variable or a column in a table.
I would expect a table - yoou can't set a variable from this - you would return a table - probably single column which would emulate the column in excel
select resultcol = 10 - convert(int,substring(col4,4,1)+substring(col4,6,1)+...) % 10
from mytbl
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
| |
Topic |
|
|
|
Excell Formula i need converted to SQL Query
