The original data is from a third party source that the company uses for daily business.
The data would be altered slightly for commission before upload. The column names are different, so they would have to be changed to match the format of the program I’m making. Also, there is no id column, so that has to be made.
You mentioned that it would be better to use column names in all lowercase. I can do that, but in order for the import to work, the column names would have to change because the third party that creates the original spreadsheet does not do this.
What I’m trying to avoid is changing the original spreadsheet too much. If that happens, then there’s no point in doing this. I don’t mind changing it slightly to make my app work better, but I don’t want it to change too much.
This program is going to serve two purposes: calculating commission and sales tax. I’m not worried about sales tax at the moment since that is a complicated subject, so I’m mainly focusing on commission right now.
According to the client, each salesperson is given a percentage of commission based on the sales amount of the job they do. If that amount is over $1000, they receive 9%. If it’s under $1000, they receive 4.5%. I have already calculated the total sales amounts per customer and the commission amount per customer both of which are based on jobs that have gone over or under $1000 as you can see in this screenshot.
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
WHERE Balance_Due = 0
GROUP BY Salesperson_1, Customer_Number
Now what I need to do is combine the two commission amount columns together for each customer so the salesperson can see how much total sales and commission they made for that customer, then I have to have a grand total of sales and a grand total of commission. This is so the salesperson can see how much they have sold for that month and how much total commission they’ve made for that month.
Hopefully, that gives you a better look at what I’m trying to do.