SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excell Formula i need converted to SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

borisowen
Starting Member

1 Posts

Posted - 06/21/2012 :  02:31:26  Show Profile  Reply with Quote
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
3355 Posts

Posted - 06/21/2012 :  03:52:54  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000