Excell Formula i need converted to SQL Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Excell Formula i need converted to SQL Query

Author

Topic

borisowen
Starting Member

1 Post

Posted - 2012-06-21 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources