T-SQL Calculations - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL Calculations

Author

Topic

mattster2020
Starting Member

6 Posts

Posted - 2012-01-04 : 09:38:09

Hi All,

I'm having a spot of bother with the below calculations, the result expected from these is different to if I work it out manually on a calcualtor:

SUM(Local_SOP30300.EXTDCOST) - (100 - Local_RM00101.Reseller_Rebate / 100) AS PARTCOST

SQL result = 1355.63

Local_SOP30300.EXTDCOST = 1455.60
Local_RM00101.Reseller_Rebate = 3.00

the calculation needed is 1455.60 / 0.97 = 1500.61

I guess the T-SQL calculation above isn't set out correctly?

Regards,

Mattster

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-04 : 09:46:16

Should the formula be:

SUM(Local_SOP30300.EXTDCOST) / (100 - Local_RM00101.Reseller_Rebate / 100) AS PARTCOST

Or it could be a problem with integer division. If Local_RM00101.Reseller_Rebate is integer, that would cause the result to be incorrect. You can force floating point division by forcing numerator and/or denominator to be float.

SUM(Local_SOP30300.EXTDCOST) / (100 - Local_RM00101.Reseller_Rebate / 100.) AS PARTCOST

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-04 : 09:48:33

select
SUM(1455.60) / ((100 - 3.00) / 100) AS PARTCOST

No, you're never too old to Yak'n'Roll if you're too young to die.

mattster2020
Starting Member

6 Posts

Posted - 2012-01-04 : 10:07:22

Thank you for your suggestions. I managed to get the corrected calculation result from webfred's post. Thank you very much :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources