Created
November 16, 2018 15:41
-
-
Save jgarth/348d9e4f50806460eb2b2761d3bb54a4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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.type NOT IN ('return_delivery', 'return_handling', 'relabel') | |
AND accounting_billables.vendor_ref IS NOT NULL | |
AND accounting_billables.created_at > NOW() - INTERVAL '90 days' | |
GROUP BY vendor_ref | |
) | |
SELECT | |
* | |
FROM | |
billed_by_via | |
WHERE | |
NOT EXISTS ( | |
SELECT 1 FROM billed_by_courier WHERE billed_by_courier.vendor_ref = billed_by_via.vendor_ref | |
) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment