Calculating paid commissions only based on the date range
Here's the scenario:
Sales Rep A sells two jobs. His commission on job 1 is a total of $200. He gets paid $100 on 4/1/2012 and the other $100 on 4/15/2012. His commission on job 2 is a total of $1000. He gets paid $200 on 4/1 and the rest of $800 on 4/15.
So if you were to pull a report for the rep it would read as follows:
|JOB||FIRST HALF DATE||FIRST AMOUNT||
SECOND HALF DATE
So here's the issue:
How do I find out what the rep has been paid for a specific date range? Basically if I was looking for what the rep was paid from 4/1 to 4/14 the answer is $300. And if I was looking for what he was paid from 4/15 to 4/30 the answer is $900. And of course if I was looking for the entire month of April the total is $1200.
I have created the basic script to query the dates of both the first and the second and give me the results. The issue I am having is that the totals in the report are calculating the amounts that are there even if they are outside the date ranges I selected.
PS: Also, the answer is not simply to check the first half date for the first half of the month and the second half for dates of 15th on since the rep can sell jobs at various times and get paid the different halfs on same pay periods.