I am creating a commission tracker for a sales team
Commission is payable on a new account on any business brought in for 3 months from the date of the first sale.
The accounts table will have the date of first sale added when the sale comes in, there will then be a future date added which will be 3 months in advance. All of the accounts will have different dates entered as the first sales for accounts will all likely occur on different days.
The table might look like:
So, what I would like to find is a way to select all Accounts where
Todays_Date >= CommissionStart AND Todays_Date <= CommissionEnd (Ideally in a format that can be used further down the line for joining tables to work out the commission amount.)
This would return: Account C Account D Account E
However, this needs to extend one step further...
There will be another table to record all of the info of the jobs date booked, price etc...
As per the above 2 tables Account B would need commision recorded only for the first job as the second falls after the End date. Account C would have all jobs accounted
So, hopefully, to simplify...
Commission is payable from the 1st to the 30th/31st of any month. But the commission for a certain account would stop accumulation on the commission end date.
Is there a way to calculate the commission for each account based on the above parameters?
My current query
SELECT SUM(J.Price) AS Sales, A.SalesPerson FROM Job J inner JOIN Account A ON J.Customer = A.Company WHERE DATE_FORMAT(JobDate, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') AND A.SalesPerson = 'SalesA' AND DATE_FORMAT(CommissionStart, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m') AND DATE(CollectionDate) <= Date(CommissionEnd)