Ok finally hammered out the solution - here is SQL that calculates multiple discounts for client Barnes & Noble
- Discount one - "disc" a percentage off list price
- Discount two - "disc_member" an additional percentage off the first discounted price
- Discount Ball - disc_ball is the percentage displayed over the book - it is the total of all available discounts for a particular item.
Phew - hope that makes sense
[codebox]SELECT items_trade.isbn,
items_trade.pr_list,
items_trade.disc,
items_trade.disc_memb,
/ pr_bn: if there's a discount, list price minus discount /
CASE
WHEN disc IS NULL THEN NULL
ELSE pr_list(1-(disc/100))
END as pr_bn,
/ pr_memb: if there's a member discount, list price minus member descount and discount /
CASE
WHEN disc_memb IS NULL THEN NULL
ELSE pr_list (1-(IfNull(disc,0)/100)) (1-(disc_memb/100))
END as pr_memb,
/ disc_ball: if there's a member discount and/or discount, the lowest total % savings /
CASE
WHEN disc IS NULL AND disc_memb IS NULL THEN NULL
ELSE (1- (1-(IfNull(disc,0)/100)) (1-(IfNull(disc_memb,0)/100)) )
END as disc_ball
FROM items_trade[/codebox]