Advanced Customer Bonus Calculation

[RSvebeck]

Hi

I need some guideance on how to approach this problem:

We have 650 customer contracts. The contracts are stored in a table like this:

CustomerContracts:

Contact ID	Customer numbers	Valid for sales on Item groups	Valid to date	Ordertype	Bonus
1020	1, 2,3	10,40,60	2013-10-31	1	10%
1030	6,4,9	20	2013-12-31	1,2,3	4%
1050	1, 2,3	10,20	2013-12-31	1,2	2%
etc

As you can see, some of the contracts are overlapping - one customer number exists on several contract ID's with small differences in the contract details.

The bonus is supposed to be calculated based on actual sales and is supposed to be presented each month. The sales transactions looks like this:

SalesTransactions:

Transaction Date	Ordertype	Customer number	Item group	Amount
2013-03-18	1	1	10	200
2013-03-19	2	1	10	300

One single transaction row can generate bonus on several different contracts.

What method can be used to calculate the bonus to be paid to for each Contract ID?

My problem is the many-to-many dilemma. One contracts is for many transcation rows, and one transaction row is for many contracts.

Appreciate any suggestions.

[#2]

Hi,

I believe the best would be to model the table 'CustomerContracts', making a line for each 'Contact ID' and 'Customer Number' as a key. So you can relate correctly.

By Rebeca

[RSvebeck]

Thank you Rebeca for your advice.

I have tried this method. I created a new table in this kind of key.

But I still have an issue and that is how do I summarize transactions for each "contract_id_customer_number_key"?

Remember that one single transaction row can refere to several "contract_id_customer_number_key".

I have many to many relations - one transaction row can be refered to many contracts, and one contract can be refered to many transactions.

If I try to do this with set analysis it does not work, since the dimensions in a chart can not be refered to in a set analysis. And building extremely nested "if...then", will just take the breath out of my server and my mind...

My current approach is to solve this in my script completely. Making a for each loop on my contracts and reading my entire statistics file for each contract. This takes time, since I have 650 contracts and around 9 million transaction rows for each contract. It currently takes 4 hours to do this, it works, but I doubt this is the bet solution.

I was hoping there was a solution with intervall match or something else so that I do not have to read all my statistics over and over again.

Robert