Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Exclamation 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.

    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. #2
    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
    Have you hugged your backup today?

  3. #3
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    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
  •