Our First SQL Query, Calculating the Commissionable %

In this case, we need a first query table that tells us the percentage of an Invoice that is commissionable.

As stated before, we don’t want to award commission for things like taxes, shipping, etc. So, that means that only a portion of an actual invoice is commissionable, and thus, only a portion of a payment (if not for the full invoice) should be commissionable as well.

You can view the entire set of code as a reference here in our Github repository or start by simply looking at the code for our first SQL table below:

SELECT
		 v."Invoice ID" 'Invoice ID',
		 v."Total (BCY)" 'Invoice Total',
		 sum(ii."Sub Total (BCY)") 'Items Subtotal',
		 sum(ii."Sub Total (BCY)") / v."Total (BCY)" 'Percent Commissionable',
		 v."Sales Person ID" 'Salesperson ID'
FROM  "Invoice Items (Zoho Books)" ii
JOIN "Invoices (Zoho Books)" v ON v."Invoice ID"  = ii."Invoice ID"  
GROUP BY v."Sales Person ID",
	 v."Invoice ID",
	  v."Total (BCY)"