2

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:

account_table

Account CommissionStart CommissionEnd
Account A 01/08/2022 01/11/2022
Account B 10/08/2022 10/11/2022
Account C 01/09/2022 01/12/2022
Account D 20/09/2022 20/12/2022
Account E 29/09/2022 29/12/2022

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...

Account Job_Date Price
Account B 01/11/2022 £x.xx
Account B 12/11/2022 £x.xx
Account C 13/11/2022 £x.xx
Account C 20/11/2022 £x.xx
Account C 29/11/2022 £x.xx

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)
6
  • Your dates data have non-valid format.. does these columns are VARCHAR?
    – Akina
    Nov 29, 2022 at 20:13
  • I would like to find is a way to select all Accounts where Todays_Date >= CommissionStart AND Todays_Date <= CommissionEnd Use BETWEEN operator.
    – Akina
    Nov 29, 2022 at 20:14
  • I guess you want both start and end date active ones. (Todays_Date >= CommissionStart AND Todays_Date <= CommissionStart ) OR (Todays_Date >= CommissionEnd AND Todays_Date <= CommissionEnd)
    – omerix
    Nov 29, 2022 at 20:34
  • @Akina I have put the dates in that format purely for display purposes, they would be formatted correctly in th live table. Nov 29, 2022 at 22:20
  • @omerix I have added some info to the bottom of the question that I had missed out originally Nov 29, 2022 at 22:22

1 Answer 1

0

Depends on the aim (which is not clear enough now) the query may be

-- select Account only
SELECT Account
FROM account_table
WHERE CURRENT_DATE BETWEEN CommissionStart AND CommissionEnd
-- select matched jobs
SELECT t2.*
FROM account_table t1
JOIN jobs_table t2 USING (Account)
WHERE CURRENT_DATE BETWEEN t1.CommissionStart AND t1.CommissionEnd
  AND t2.Job_Date BETWEEN t1.CommissionStart AND t1.CommissionEnd
-- select matched jobs total price
SELECT Account, SUM(t2.Price) As total_price
FROM account_table t1
JOIN jobs_table t2 USING (Account)
WHERE CURRENT_DATE BETWEEN t1.CommissionStart AND t1.CommissionEnd
  AND t2.Job_Date BETWEEN t1.CommissionStart AND t1.CommissionEnd
GROUP BY 1

In the last query only accounts which have at least one job in the period will be listed. If you need in all accounts then use LEFT JOIN and wrap SUM() with COALESCE().

Of course you may use any definite date value in date literal form instead of CURRENT_DATE.

1
  • I have added the query that I am currently using to the bottom of the question but I am not sure how well this query will hold out in the long term as the database grows... Essentially, I am looking to keep track of what commission is owed. The above query calculated the sales for the month from the particular sales person... But, if a sales falls within the month but after the commission end date, it is not included to the total... Hopefully the query gives an idea as to what Im after. Im also not sure if I have used the correct join Nov 30, 2022 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.