I've been asked to create a financial report, which needs to give a total commission rate between two dates for several 'referrers'. That's the easy part.

The difficult part is that the commission rate varies depending not only on the referrer but **also** on the type of referral **and also** on the number of referrals of that type that have been made by a given referrer. 

The tracking of the number of referrals needs to take into account ALL referrals, rather than those in the given date range - in other words, the commission rate is on a sliding scale for each referrer, changing as their total referrals increase. Luckily, there are only a maximum of 3 commission levels for each type of referral.

The referrals are all stored in the same table, 1 row per referral, with a field denoting the referrer and the type of referral. An example to illustrate:

```
ID   Type    Referrer    Date
1    A       X           01/12/08
2    A       X           15/01/09
3    A       X           23/02/09
4    B       X           01/12/08
5    B       X           15/01/09
6    A       Y           01/12/08
7    A       Y           15/01/09
8    B       Y           15/01/09
9    B       Y           23/02/09

```

The commission rates are not stored in the referral table - and indeed may change - instead they are stored in the referrer table, like so:

```
Referrer    Comm_A1    Comm_A2    Comm_A3    Comm_B1    Comm_B2    Comm_B3
X           30         20         10         55         45         35
Y           45         35         25         60         40         30

```

Looking at the above referral table as an example, and assuming the commission rate level increased after referral number 1 and 2 (then remained the same), running a commission report for December 2008 to February 2009 would return the following:

**[Edit]** - to clarify the above, the commission rate has three levels for each type and each referrer, with the initial rate Comm\_A1 for the first referral commission, then Comm\_A2 for the second, and Comm\_A3 for all subsequent referrals.

```
Referrer    Type_A_Comm    Type_A_Ref    Type_B_Comm    Type_B_Ref
X           60             3             100            2
Y           80             2             100            2

```

Running a commission report for just February 2009 would return:

```
Referrer    Type_A_Comm    Type_A_Ref    Type_B_Comm    Type_B_Ref
X           10             1             0              0
Y           0              0             40             1

```

**Edit** the above results have been adjusted from my original question, in terms of the column / row grouping. 

I'm quite sure that any solution will involve a sub-query (perhaps for each referral type) and also some kind of aggregate / Sum If - but I'm struggling to come up with a working query.

**[Edit]** I'm not sure about writing an equation of my requirements, but I'll try to list the steps as I see them:

Determine the number of previous referrals for each type and each referrer - that is, irrespective of any date range.

Based on the number of previous referrals, select the appropriate commission level - 0 previous = level 1, 1 previous = level 2, 2 or more previous = level 3 

(Note: a referrer with no previous referrals but, say, 3 new referrals, would expect a commission of 1 x level 1, 1 x level 2, 1 x level 3 = total commission)

Filter results according to a date range - so that commission payable for a period of activity may be determined.

Return data with column for referrer, and a column with the total commission for each referral type (and ideally, also a column with a count for each referral type).

Does that help to clarify my requirements?