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.
| Author |
Topic |
|
eamonn
Starting Member
3 Posts |
 Posted - 2013-08-28 : 05:43:43
|
| hi guysI have a formula which is working in access database and im trying to import into a database on a sql server management studio and im wondering could anyone help me convert the following formula into the appropriate formula required for sql server thanks for any help in advanceIIf([price] Like "opf*",IIf([Total]>=50,IIf([ Aim] In ("50","60","70"),Round(IIf([Hours Total]>105,(105/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])),([Total]/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]))),4),IIf([MOA]="21",0,Round(IIf([yes/no]="yes",([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]),IIf([Total]>450,(450/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])),([ Total]/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])))),4))),0),0)eamonn |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-28 : 08:54:36
|
quote:
Originally posted by eamonn
hi guysI have a formula which is working in access database and im trying to import into a database on a sql server management studio and im wondering could anyone help me convert the following formula into the appropriate formula required for sql server thanks for any help in advanceIIf([price] Like "opf*",IIf([Total]>=50,IIf([ Aim] In ("50","60","70"),Round(IIf([Hours Total]>105,(105/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])),([Total]/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]))),4),IIf([MOA]="21",0,Round(IIf([yes/no]="yes",([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]),IIf([Total]>450,(450/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])),([ Total]/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])))),4))),0),0)eamonn
I have not gone through the entire logic of your expression, but if it worked correctly in Access, it should work correctly in SQL 2012 as well with two changes:1. Replace all the double-quotes with single quotes.2. I am not sure of the behavior in Access when you divide one integer by another - for example 450/720. In SQL Server, that would yield zero (because it is considered integer division). If that is not your desired outcome, change at least one of the two to a non-numeric type. For example use 450.0/720 instead of 450/720 |
 |
|
|
eamonn
Starting Member
3 Posts |
Posted - 2013-08-28 : 10:59:28
|
| I have tried with single quotes and added.0 and it still isn't working could you help me please |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-28 : 11:03:28
|
quote:
Originally posted by eamonn
I have tried with single quotes and added.0 and it still isn't working could you help me please
Is it giving you a syntax error, or is it giving you the wrong results? If it is a syntax error, can you post the EXACT error message? |
|
|
|
eamonn
Starting Member
3 Posts |
Posted - 2013-08-28 : 16:06:03
|
| hi jamesthe only error message i am receving is incorrect syntax near '''eamonn |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-28 : 16:14:17
|
If you replaced all the double-quotes with single quotes, that should not happen. The following parses without any errors for me. But I don't know if it works because I don't have the tables or the data to run it. Where is the error coming from? You can double-click on the error in the SSMS output window and it will take you to the line on which the error happenedSELECT IIf([price] Like 'opf*',IIf([Total]>=50,IIf([ Aim] In ('50','60','70'), Round(IIf([Hours Total]>105,(105.0/720)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])), ([Total]/720.0)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]))),4),IIf([MOA]='21',0,Round(IIf([yes/no]='yes', ([Discount]*[Deduction])+(0.011*[Discount]*[Deduction]),IIf([Total]>450,(450.0/720)*(([Discount]*[Deduction])+ (0.011*[Discount]*[Deduction])),([ Total]/720.0)*(([Discount]*[Deduction])+(0.011*[Discount]*[Deduction])))),4))),0),0) |
|
|
|
|
|
|
|
|
Formula for calculated fields