subtraction of column doesn''t work correctly

Question

Post reply

subtraction of column doesn''t work correctly

AVB

SSChampion

Points: 11418
More actions
January 14, 2005 at 8:18 am

#65508

I have a basic view that subtracts column A from column B and returns the difference in column C. But when I sum all columns and manually subtract B from A it doesn't equal the sum of column C.
Create VIEW v_MTSTM_ORDERPROFIT
select top 100 percent o.order_number,customer_charges - carrier_pay as Profit,customer_charges,carrier_pay,o.company,o.address1,o.address2,o.city,o.state,zip,o.corp_id,o.entered,r.pickupdateactual --,o.Account
from v_MTSTM_ORDERBYBILLTO o inner join v_mtstm_profitloss r on o.order_number = r.order_number
order by o.order_number
Then I run this to double check and subtact with a calculator Carrier_Pay from Customer_chrg but the Sum of Profit is about 2000 dollars off.
select sum(carrier_pay) as Carrier_Pay,sum(customer_charges)as Customer_Chrg,sum(profit) as profit from v_MTSTM_ORDERPROFIT
where pickupdateactual between '2004-01-01' and '2004-12-31 23:59:59.997'
Do I need to specfiy the scale or that the datatype is money?? Any thoughts would be appreciated thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

What are the underlying data types look like?

And are there NULLs present?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AVB SSChampion Points: 11418 More actions · Answer 2

The view I pull off of v_MTSTM_ProfitLoss actually converts the table datatype of FLOAT to a SMALLMONEY (columns Carrier_Pay and Customer_Charges). There are no nulls at all.

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 3

The problem is most likely that the datatype in one of the columns is FLOAT which using binary math instead of Decimal math with some very unexpected results. Recommend you change the FLOAT column to a DECIMAL column, permanently.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 4

Thanks Jeff I changed the column to a decimal in the table but I am still having the same problem. Should I format the Profit column that gets created in the query? Does it have a default data type if you don't specify one? The Profit column isn't in any of the views I reference it is created only in this paticular query sort of a virtual column I guess. Thanks again!