Problem with variable of type money?

Here's what I want to do: I've got a table with orders, each order has
a specific discountrate (an int, which represents a percentage). Each
order consists of 1 or more items in another table, each item in that
table has a price. Now I want to return the full price and the
discounted price (or the discounted amount).

Here's a relevant excerpt of the code:

--------------------
CREATE TABLE #tmp (OrderID Integer,
Price money,
Discount money)

DECLARE @.Discount money

SELECT @.Discount =
(
(
(SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
FROM OrderDetails
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
+
(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
OrderDetailsAmount),0)
FROM OrderDetailsSupplement
INNER JOIN OrderDetails ON
OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
)
*
( @.DiscountRate / 100 )
)

SELECT CustomerFull,
SUM(Price) As Price,
SUM(Discount) As Discount,
SUM (Products) As Products,
COUNT(@.orderID) As Orders
FROM #tmp
GROUP BY CustomerFull
ORDER BY CustomerFull
--------------------

The problem: instead of getting a low number (like 0.57 for instance),
I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
code, but I'd really like to know what I'm doing wrong.

--
BVHAm 2 Mar 2006 07:23:09 -0800 schrieb bartvanhemelen@.gmail.com:

> Here's what I want to do: I've got a table with orders, each order has
> a specific discountrate (an int, which represents a percentage). Each
> order consists of 1 or more items in another table, each item in that
> table has a price. Now I want to return the full price and the
> discounted price (or the discounted amount).
> Here's a relevant excerpt of the code:
> --------------------
> CREATE TABLE #tmp (OrderID Integer,
> Price money,
> Discount money)
> DECLARE @.Discount money
> SELECT @.Discount =
> (
> (
> (SELECT SUM(OrderDetails.Price * OrderDetailsAmount)
> FROM OrderDetails
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> +
> (SELECT ISNULL(SUM(OrderDetailsSupplement.Price *
> OrderDetailsAmount),0)
> FROM OrderDetailsSupplement
> INNER JOIN OrderDetails ON
> OrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsID
> WHERE OrderID = @.orderID AND CustomerID = @.CustomerID)
> )
> *
> ( @.DiscountRate / 100 )
> )
> SELECT CustomerFull,
> SUM(Price) As Price,
> SUM(Discount) As Discount,
> SUM (Products) As Products,
> COUNT(@.orderID) As Orders
> FROM #tmp
> GROUP BY CustomerFull
> ORDER BY CustomerFull
> --------------------
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

In your example i can't see where @.DiscountRate is declared or set. From
where should the value for @.DiscountRate come?

bye,
Helmut|||BVH,

You are probably the victim of integer arithmetic.

Change @.DiscountRate / 100 to @.DiscountRate / 100.0.

That "point zero" tells the system that you want float division,
instead of integer division. The better solution would be to cast the
integer to a float but adding .0 will work.|||(bartvanhemelen@.gmail.com) writes:
> *
> ( @.DiscountRate / 100 )
> )
>...
> The problem: instead of getting a low number (like 0.57 for instance),
> I get a 0. Right now I've "solved" this by replacing "( @.DiscountRate /
> 100 )" with just "@.DiscountRate" and then dividing by 100 in my asp
> code, but I'd really like to know what I'm doing wrong.

@.DiscountRate was integer, correct?

You should have left out the parentheses above. This mandates SQL Server
to compute this expression before it gets mixed with the rest. But if
you divide two integers, you get integer division, which is not what you
want at all.

Assuming that what is before the * is money, leaving out the parenthesis,
transforms the division to money division.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||what happens next week when the boss wants to give a five and a half
percent discount???

percentages are ALWAYS better stored as real, and multiplied by 100 for
display to the users.|||I agree with Doug. We usually use reals to hold our percentages. In the
db, a 5.5% discount would look like .055.......

Because, in effect, that's really what 5.5% represents.|||figital (mharen@.gmail.com) writes:
> I agree with Doug. We usually use reals to hold our percentages. In the
> db, a 5.5% discount would look like .055.......
> Because, in effect, that's really what 5.5% represents.

In our shop we can never make up our mind... So some of the percentages
are stored as aba_percent, others as aba_fraction and yet others as float.
aba_percent is just an alias for "float" but the name implies that it is
a percentage, and that you should divide with 100 before use. aba_fraction
is float, and constrained to be between 0 and 1. Multiply with 100 before
display, and divide by 100 before storing. Those that just float, can
hold values outside the range [0..1]. (I have considered a constraint
to keep them between -10 and 10, but that is a risky business, as one
day 1200% may be a correct value.)

The problem with storing percentages as fraction, is that some developers
make the entry forms a carbon of the data model, so they don't display
the fraction as a percentage, but as a fraction...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>ba_fraction is float, and constrained to be between 0 and 1

Well, actually, I've used percentages to keep track of growth rates, or
percentage growths where the percentage can greatly exceed one.

For instance, 2.00 means 200 percent, which means multiply by 200
percent.

Another really good thing about storing 5 percetn as .05 is that the
very first time the programmer/UI dweeb displays it that way, the
programmer dweeb sees it.

At the VERY worst, the end user sees it, and pretty much ANYONE used to
dealing with money, growths, or numbers will report it as a pretty
minor bug, and realize what is going on.
Again, differing points of view arrived at by rational people, but IMO
float is a much better solution.
regards,
doug