Displaying items between 2 dates

Question

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)

Answer 1

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.