margin-per-ups-parcel.sql

WITH billed_by_ups AS (
  SELECT 
    vendor_ref, SUM(amount) AS sum_amount, vendor_invoice_id
  FROM 
    accounting_vendor_billables
  INNER JOIN
  	accounting_vendor_invoices ON accounting_vendor_invoices.id = accounting_vendor_billables.vendor_invoice_id
  WHERE 
  	accounting_vendor_invoices.type = 'Accounting::VendorInvoices::UPS'
  GROUP BY 
    vendor_ref, vendor_invoice_id
), billed_by_via AS (
  SELECT 
    vendor_ref, SUM(amount) AS sum_amount
  FROM 
  accounting_billables
  WHERE 
	parent_type = 'LastMile::Delivery'
  GROUP BY 
  	vendor_ref
), difference_in_billing AS (
  SELECT 
    billed_by_ups.vendor_ref, 
    vendor_invoice_id,
    billed_by_ups.sum_amount AS ups_amount, 
    billed_by_via.sum_amount AS via_amount,
    billed_by_via.sum_amount - billed_by_ups.sum_amount AS difference_in_favor_of_via
  FROM 
    billed_by_ups
  LEFT JOIN 
    billed_by_via ON billed_by_via.vendor_ref = billed_by_ups.vendor_ref
)

SELECT
	(SELECT filename FROM accounting_vendor_invoices WHERE accounting_vendor_invoices.id = difference_in_billing.vendor_invoice_id LIMIT 1) AS filename,
	SUM(ups_amount) AS billed_by_ups,
	SUM(via_amount) AS billed_by_via,
	SUM(difference_in_favor_of_via) AS difference_in_favor_of_via,
	(SELECT metadata ->> 'parcels_shipped' FROM accounting_vendor_invoices WHERE accounting_vendor_invoices.id = difference_in_billing.vendor_invoice_id LIMIT 1) AS parcels_shipped,
	(SUM(difference_in_favor_of_via)::numeric / (SELECT (metadata ->> 'parcels_shipped')::integer FROM accounting_vendor_invoices WHERE accounting_vendor_invoices.id = difference_in_billing.vendor_invoice_id LIMIT 1))::numeric / 100 AS margin_per_parcel
FROM 
	difference_in_billing
GROUP BY 
	vendor_invoice_id
ORDER BY 
	filename DESC;