I’m creating commission reports for my program now and I need to create one that shows the Customer Number, the Customer’s Name, the Total Sales per customer and the total commission amount per customer.
I only have one problem. It needs to be done per salesperson.
With the event that some people can leave the company, should I include a second table that lists the different salespeople so that the code knows who is who, or am I overthinking something here? My thought is if I can code a form or a table in PHP that is connected to a second table, it will be easier to create queries for each salesperson rather than hardcoding it through PHP and having to update the code each time someone is terminated or hired.
This query shows how the commission is calculated (this is a demo calculation):
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END as comm_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000,
CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as comm_below_1000
FROM invoices
WHERE Balance_Due = 0
GROUP BY Salesperson_1, Customer_Number
What do you guys suggest?