Calculate Commissions Awarded for Payments
Now we can build our next SQL table (the final one) and start the first subquery off by joining our first SQL table (the one calculating the Commissionable %) to the Invoice Payments table via the common Invoice ID field.
And as we saw in the prior video, the Invoice Payments table links to the Customer Payments table via the Payment ID field.
Here is the code for this first subquery:
SELECT c."Payment Date" 'Date Paid', c."Amount (BCY)" 'Payment Amount', p."Percent Commissionable" '% Commissionable', p."Percent Commissionable" * c."Amount (BCY)" * .05 'Commission', s."Name" 'Salesperson', 'Invoice Payments' 'Category' FROM "Customer Payments (Zoho Books)" c JOIN "Invoice Payments (Zoho Books)" i ON i."Payment ID" = c."Payment ID" JOIN invoice_percentages p ON p."Invoice ID" = i."Invoice ID" JOIN "Sales Persons (Zoho Books)" s ON p."Salesperson ID" = s."Sales Person ID"