Home » SQL & PL/SQL » SQL & PL/SQL » Help with "What If" analysis
Help with "What If" analysis [message #340435] Tue, 12 August 2008 18:33 Go to next message
Messages: 2
Registered: July 2007
Location: UK
Junior Member
Hi all,

I'm after a bit of help with something that's had me stumped for the last few days...

I'm working for a retail organisation and we're trying to write a routine to determine the best combination of discounts to apply to a customers order where multiple 'buy 2 from a spread of products and get $x off' and 'buy 3 from a spread of products and get cheapest item free' offers exist. The buy 2 get $x off represent one type of offer (known as money off offer AKA MOO) and buy 3 get cheapest free form another type of offer (free item offer AKA FIO). The problem is that the two different offer types can overlap on individual items, meaning you have to decide which type of offer to apply (as you cannot apply both to the same product). You have to apply the offer to ALL participating items in order to achieve the discount.

On my theoretical order there are 9 items, three MOO's and three FIO's for a variety of discount values that overlap on certain products, see attached .jpg. I can manually work out that the combination of MOO 02 + FIO 02 + FIO 03 gives the best discount ($136) but need to find a way of implementing this in PL/SQL. I've wracked my brain but inevitably get lost in a fog of endless recursive loops...

I have got as far as building two collections that represent the MOO and FIO offers, both collections have the same structure:

product_id VARCHAR2 --> e.g. PROD1
offer_code VARCHAR2 --> e.g. MOO 01
disc_amt NUMBER(10,2) --> e.g. $20

For example, the MOO collection would be:

PROD1 / MOO 01 / $5
PROD2 / MOO 01 / $5
PROD3 / MOO 02 / $10
PROD4 / MOO 02 / £10
PROD8 / MOO 03 / $50
PROD9 / MOO 03 / $50

Any help would be greatly appreciated...

Cheers Bob R

Re: Help with "What If" analysis [message #340455 is a reply to message #340435] Wed, 13 August 2008 00:18 Go to previous message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't really understand what you want (nor I have a test case) but have a look at:
Oracle® Database Data Warehousing Guide
Chapter 22 SQL for Modeling

Previous Topic: how to find memory allocation of table
Next Topic: problem identifying a symbol in the content of a column
Goto Forum:

Current Time: Fri Aug 18 07:50:12 CDT 2017

Total time taken to generate the page: 0.04830 seconds