Posted by helmut woess on 03/02/06 18:52

Am 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

[Back to original message]