AnsweredAssumed Answered

Calculating paid commissions only based on the date range

Question asked by nickodm on Apr 23, 2012
Latest reply on Apr 23, 2012 by philmodjunk

Title

Calculating paid commissions only based on the date range

Post

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

SECOND AMT TOTAL
Job 1 4/1/2012 $100 4/15/2012 $100 $200
Job 2 4/1/2012 $200 4/15/2012 $800 $1000

 

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. 

 

Outcomes