Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Order cycle management report in a single SQL query
select spree_line_items.order_id, spree_products.supplier_id, sum(spree_line_items.price * spree_line_items.quantity) AS amount
from spree_line_items
inner
join spree_variants
on spree_variants.id = spree_line_items.variant_id
inner
join spree_products
on spree_products.id = spree_variants.product_id
inner
join enterprises
on enterprises.id = spree_products.supplier_id
WHERE (
NOT (lower("public"."enterprises"."name") like '%prueba%')
AND NOT (lower("public"."enterprises"."name") like '%test%')
AND NOT (lower("public"."enterprises"."email_address") like '%prova%')
AND NOT (lower("public"."enterprises"."email_address") like '%mailinator.com%')
AND NOT (lower("public"."enterprises"."email_address") like '%prueba%')
AND NOT (lower("public"."enterprises"."email_address") like '%test%')
AND "public"."enterprises"."is_primary_producer" = TRUE
AND date_trunc('month', CAST("public"."enterprises"."created_at" AS timestamp)) = date_trunc('month', CAST((NOW() + INTERVAL '-1 month') AS timestamp))
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.