Scenario:
A factory producing steel pipes every day in various sizes and gauges (swg), are logged and stored in the pipe_production table (pipe_id foreign key to pipe table). The factory also has clients to which it regularly sells pipes, upon request an invoice is created and logged in the invoices table. Subsequently, the related pipes relative to each invoice are stored in the pipe_invoices table. When a member of the sales team authorises the invoice, the authorised column switches from false to true (0 => 1 in) in the invoices table and signals that that these pipes will be sold and should be removed from the stock.
I'm looking for a query to produce a stock table to accurately assess the pipe onsite. However I'm only looking to find only the difference between pipes produced and AUTHORISED invoice pipes.
The application is being built on the Laravel framework.
Table: pipes
id | description | swg | min_weight | max_weight
1 | 2" X 2" | 16 | 10 | 11
2 | 2" X 2" | 18 | 8 | 19
3 | 1" X 2" | 18 | 4 | 6
Table: pipe_productions
id | pipe_id | quantity | production_date
1 | 1 | 1000 | 2020-10-1
2 | 2 | 2000 | 2020-10-1
3 | 3 | 5500 | 2020-10-1
Table: invoices
id | client_id | authorised | loaded | invoice_date
1 | 1 | 0 | 0 | 2020-10-09
2 | 2 | 1 | 0 | 2020-10-09
3 | 2 | 1 | 1 | 2020-10-09
Table: pipe_invoices
id | invoice_id | pipe_id | quantity
1 | 1 | 3 | 2000
2 | 1 | 1 | 1000
3 | 2 | 2 | 1000
Edit: My Current query which only gets the difference between pipe_production and pipe_invoices. It does not account for the case where the invoice is not authorised and should not be removed.
SELECT *, coalesce(a.quantity, 0)-coalesce(b.quantity, 0) as diff
FROM
(SELECT pipe_id, sum(quantity) as quantity
FROM pipe_productions
GROUP BY pipe_id) a
LEFT JOIN
(SELECT pipe_id, sum(quantity) as quantity
FROM pipe_invoices
GROUP BY pipe_id) b
on a.pipe_id = b.pipe_id
LEFT JOIN pipes
on a.pipe_id = pipes.id
WHERE coalesce(a.quantity, 0)-coalesce(b.quantity, 0) != 0
ORDER BY swg asc, pipe_description desc
I assume that you only need to a small adaption to your query and join invoices in your b statement:
SELECT *, coalesce(a.quantity, 0)-coalesce(b.quantity, 0) as diff
FROM
(
SELECT pipe_id, sum(quantity) as quantity
FROM pipe_productions
GROUP BY pipe_id
) a
LEFT JOIN
(
SELECT pipe_id, sum(quantity) as quantity
FROM pipe_invoices pi
JOIN invoices i ON pi.invoice_id = i.id
WHERE i.authorised = 1
GROUP BY pipe_id
) b
on a.pipe_id = b.pipe_id
LEFT JOIN pipes
on a.pipe_id = pipes.id
WHERE coalesce(a.quantity, 0)-coalesce(b.quantity, 0) != 0
ORDER BY swg asc, pipe_description desc