Posted by bartvanhemelen on 03/02/06 17:23

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.

--
BVH

• Next in forum: MSSQL Query Memory when using long fields
• Prev in forum: Re: what are cursors in sql???
• Thread view: Problem with variable of type money?

[Reply to this message]