PostgreSQL 11
How to pass o.create_date value into INNER JOIN? I need Max ID before o.create_date
SELECT o.id,
o.create_date date,
sum(oi.quantity) qty,
sum(oi.quantity * sp.price) total
FROM ax_order o
LEFT JOIN ax_order_invenotry oi on o.id = oi.order_id
LEFT JOIN ax_inventory i on i.id = oi.inventory_id
LEFT JOIN ax_suppliers s on s.id = o.supplier_id
INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
INNER JOIN
(
SELECT inventory_id,
max(id) id
FROM ax_supplier_price
WHERE create_date <= o.create_date
GROUP BY inventory_id
) lsp ON (sp.id = lsp.id)
WHERE o.store_id = 13
AND o.supplier_id = 35
GROUP BY o.id, o.create_date
ORDER BY o.id
You could use the LATERAL join mechanism to make it work:
WITH ax_order AS (
SELECT *
FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id)
), ax_order_inventory AS (
SELECT *
FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity)
), ax_supplier_price AS (
SELECT *
FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date)
)
SELECT o.id,
o.create_date date,
sum(oi.quantity) qty,
sum(oi.quantity * sp.price) total
FROM ax_order o
LEFT JOIN ax_order_inventory oi on o.id = oi.order_id
INNER JOIN ax_supplier_price sp ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
INNER JOIN LATERAL
(
SELECT inventory_id,
max(lsp.id) id
FROM ax_supplier_price lsp
WHERE sp.create_date <= o.create_date
GROUP BY inventory_id
) lsp ON sp.id = lsp.id
GROUP BY o.id, o.create_date
ORDER BY o.id
I deleted some JOINs that were not strictly necessary and mocked your data as well as I could see. Note, however, that you could also use a WHERE clause to find it - which should be more efficient:
WITH ax_order AS (
SELECT *
FROM (VALUES (1, '2000-1-1'::date, 1, 1)) as x(id, create_date, store_id, supplier_id)
),
ax_order_inventory AS (
SELECT *
FROM (VALUES (1, 2, 4)) as x(order_id, inventory_id, quantity)
),
ax_supplier_price AS (
SELECT *
FROM (VALUES (1, 2, 1, 10, '1999-12-31'::date)) as x(id, inventory_id, supplier_id, price, create_date)
)
SELECT o.id,
o.create_date date,
sum(oi.quantity) qty,
sum(oi.quantity * sp.price) total
FROM ax_order o
LEFT JOIN ax_order_inventory oi on o.id = oi.order_id
INNER JOIN ax_supplier_price sp
ON (sp.inventory_id = oi.inventory_id and sp.supplier_id = o.supplier_id)
WHERE sp.id =
(
-- NOTE: no GROUP BY necessary!
SELECT max(lsp.id) id
FROM ax_supplier_price lsp
WHERE sp.create_date <= o.create_date
AND lsp.inventory_id = sp.inventory_id
)
GROUP BY o.id, o.create_date
ORDER BY o.id