Thread: Totally Lost -- Please Help
07-19-04, 20:13 #1Registered User
- Join Date
- Jul 2004
Unanswered: Totally Lost -- Please Help
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.
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
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
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)
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.
07-19-04, 21:53 #2Registered User
- Join Date
- Dec 2002
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.
hmscottHave you hugged your backup today?
07-19-04, 21:55 #3Registered User
- Join Date
- Jul 2003
- San Antonio, TX
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 21:57."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
07-20-04, 08:15 #4Registered User
- Join Date
- Jul 2004
I will look at the suggested methods.