Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- )
Add Comment
Please, Sign In to add comment