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"