I had use DISTINCOUNT to get the total new customer for the sales person.
I had another table to store the commission rate by period and the condition.
The result as above.
- for those new customers equal to or more than 100, commission rate would be 25
- for those new customers between 1 to 99, commission rate would be 20
- the date would be between 1-6-20 to 31-8-20