1. Registered User
Join Date
Jul 2004
Posts
5

I am looking thru SQL Books, browsing the forum, but still unclear of the best method to perform the following calculation. I am really struggling with this and any helpful hints would be greatly appreciated.

Situation
Table 1 = customer information. Contains customer #, name, address, etc.
Table 2 = default price list. Contains item #, item desc, price
Table 3 = Allowed discounts based on customer types. Hospital gets \$1 off, Doctors get 25 cents off, individuals get no discount.

Further discounts are given for multiple purchases. The 1st item is 100%, 2nd item is 75% and 3rd and subsequent items get 50% off purchase that day.

Here is a basic query result set of customer number, price, item ordered, item description and customer type.

Cust Price ItemNo Item Type
1 \$5.50 23432 Moisturizer Hospital
2 \$5.55 23453 Moisturizer 2 Indvidual
2 \$29.00 23243 Lotion Pkg Indvidual
3 \$29.00 23243 Lotion Pkg Doctor
3 \$21.25 23232 Gloves Doctor
3 \$3.00 23235 Drape Doctor
3 \$2.00 27821 Lube Doctor

Calculation

Customer 1 ordered 1 item @ 5.50, but because they are a hospital they get a \$1.00 discount. They only ordered 1 item so they pay 100% of the discounted price.

(5.50 - 1.00) * 100% = 4.50

Customer 2 ordered 2 items @ 5.55 and 29.00 respectively. They get no discount because they are an individual client, but do qualify for the multiple item discount of 25% for the second item.

29.00 + 5.55 * 75% = \$33.16

Customer 3 ordered 4 items @ 29.00, 21.25, 2.00 and 3.00 respectively. They get a 25 cent discount off each item for being a doctor. They also get a multiple item discount of 25% off the second item and and 50% off items 3 and 4.
The math ideally will look like this

(29.00 - .25)*100% + (21.25 - .25)*75% + (3.00-.25)*50% + (2.00 - .25) * 50% = \$47

Problem
For customer 2 if someone entered the lower valued item first the math would look like this

5.55 + 29.00 * 75% = \$27.30 (loss of revenue)

Possible Solution
The prices need to be sorted in descending order to maximize revenue prior to discounts being taken for a given day.
Should I use a "rank by clause" statement?

Then I need to multiply the first item by 100%, the 2nd by 75% and the remaining items by 50% to get a grand total of money due. Any pointers would be greatly appreciated.
I am not at all sure how to accomplish this.

2. Registered User
Join Date
Dec 2002
Posts
1,245
I see an updateable cursor inside a stored procedure in your future. I think you would pass into your SP the ID of the customer and possibly the order number (depends on whether you want to apply the discounts across orders or just within a single order).

Alternatively, I can stretch my imagination a bit more and I can foresee (possibly) doing this inside an inline user-defined function (again, pass in the customer number and maybe the order number).

Finally, I can also see the wisdom in creating a COM+ function to perform more general calculations depending on various parameters that are passed into the function (which parameter values are stored on a back-end db).

I think for us to provide more help, you need to provide the DDL for the various tables and a bit more sample data.

Regards,

hmscott

3. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Loss of revenue as you described it is just what INet shopping is all about...or scam, which what it would be if you start applying largest percentage discount to the smallest price in a purchased bundle.

But the math should be straight forward. You should (I would anyway) create a percentage discount table, it can be easily generated. Make it have 3 columns:
QuantityStart, QuantityEnd, and DiscountPercent. Fill it up with start and end quantities to be the same value, except for the last row where QuantityStart would be something like 5 and QuantityEnd would be 9999999999, for example.

You should also record ItemLineNumber as it being recorded at the time of the purchase.

select CustID, ItemLineNumber, ItemNo, Item, Type, ItemPrice=
(price - case Type when 'Hospital' then 1.00 when 'Doctor' then 0.25 else 0.00 end) * DiscountPercent
from CustomerInformation ci
inner join Orders o
on ci.CustID = o.CustID
inner join OrderDetails od
on o.OrderID = od.OrderID
inner join PercentageDiscount pd
on od.Quantity between QuantityStart and QuantityEnd
order by od.ItemLineNumber
Last edited by rdjabarov; 07-19-04 at 22:57.

4. Registered User
Join Date
Jul 2004
Posts
5
I will look at the suggested methods.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•