unbilled-by-couriers-sum.sql

WITH billed_by_courier AS (
  SELECT 
    vendor_ref
  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 IN ('Accounting::VendorInvoices::UPS', 'Accounting::VendorInvoices::DPD')
  GROUP BY 
    vendor_ref
), billed_by_via AS (
  SELECT 
    (array_agg(date_trunc('day', accounting_billables.created_at)))[1] AS billed_at, 
    vendor_ref, 
    SUM(amount) AS sum_amount, 
    (array_agg(last_mile_deliveries.service))[1] AS service
  FROM 
  	accounting_billables
  INNER JOIN 
  	last_mile_deliveries ON last_mile_deliveries.id = accounting_billables.parent_id AND accounting_billables.parent_type = 'LastMile::Delivery'
  WHERE
  	last_mile_deliveries.service != 'FCE'
  	AND accounting_billables.vendor_ref IS NOT NULL
	AND accounting_billables.created_at > NOW() - INTERVAL '90 days'
  GROUP BY vendor_ref
)

SELECT 
	SUM(sum_amount)
FROM 
	billed_by_via
WHERE 
	NOT EXISTS (
	  SELECT 1 FROM billed_by_courier WHERE billed_by_courier.vendor_ref = billed_by_via.vendor_ref
	)
GROUP BY service