Calculate Your “Negative” Commissions Using Refunds
I find it very useful to build out separate sub queries in a different SQL editor in order to test everything out and make sure it’s working properly. Then, once I get it working, I can add it in to our real sequel table, stacking the data on top of what we already built.
Here is the code for that second subquery:
SELECT r."Refund Date" 'Date Paid', r."Amount (BCY)" 'Payment Amount', p."Percent Commissionable" '% Commissionable', p."Percent Commissionable" * r."Amount (BCY)" * -.05 'Commission', s."Name" 'Salesperson', 'Refunds' 'Category' FROM "Credit Notes Refund (Zoho Books)" r JOIN "Credit Notes (Zoho Books)" c JOIN invoice_percentages p ON p."Invoice ID" = c."Invoice ID" JOIN "Sales Persons (Zoho Books)" s ON s."Sales Person ID" = p."Salesperson ID"