SQL Query that calculates 'free' or 'reserved' items in a collection
We are creating a inventory system for items called readoutprobes and readoutprobekits. See the schema below.
A readoutprobekit, is a predefined collection of 1 or more readoutprobes, i.e. a kit. In a kit, a specific type of a readoutprobe, can only occur once.
The inventory for readoutprobes are tracked by a readoutprobe_containers table, monitoring a containers volume.
The inventory for readoutprobekits, are tracked by a readoutprobekit_containers table. However, a readoutprobekit do not track physical readoutprobe containers. Instead, its assumed that a physical readoutkit is properly 'assembled' using a set of physical readoutprobes.
Getting the count of physical readoutprobe containers, with a volume > 0, for a specific readoutprobe, is obtained from the readoutprobe_container table, and the same for the kits
However, we want to get a 'reserved count' number, (or 'free') for each readoutprobe, reflecting the kits that do exist, and do contain a specific readoutprobe.
For example, say we got a readoutprobe, named A, having a count of 42. Now, when creating a specific readoutprobe kit containing a readoutprobe named A, we want to have a count of 'reserved' being 1, for readoutprobe A (or free = 41).
If there is a way of adding to the query for readoutprobes and their stock, that would be preferable.
The 'master query' for readoutprobes looks like this:
SELECT readoutprobes.*,
v.total_volume,
stock_containers.stock_count,
aliquots.aliquots_count
FROM readoutprobes
LEFT JOIN (
SELECT p.id, COUNT(*) stock_count, stock_containers.readoutprobe_id
FROM readoutprobes AS p, readoutprobe_containers AS stock_containers
WHERE p.id = stock_containers.readoutprobe_id AND stock_containers.volume > 0 AND stock_containers.parent_container IS NULL
GROUP BY p.id
) AS stock_containers
ON stock_containers.readoutprobe_id = readoutprobes.id
LEFT JOIN (
SELECT p.id, COUNT(*) aliquots_count, aliquot_containers.readoutprobe_id
FROM readoutprobes AS p, readoutprobe_containers AS aliquot_containers
WHERE p.id = aliquot_containers.readoutprobe_id AND aliquot_containers.volume > 0 AND aliquot_containers.parent_container IS NOT NULL
GROUP BY p.id
) AS aliquots
ON aliquots.readoutprobe_id = readoutprobes.id
LEFT JOIN (
SELECT readoutprobes.id, COALESCE(SUM(pt.volume),0) total_volume
FROM readoutprobes, readoutprobe_containers AS pt
WHERE readoutprobes.id = pt.readoutprobe_id
GROUP BY readoutprobes.id
) AS v
ON readoutprobes.id = v.id
GROUP BY readoutprobes.id
ORDER BY readoutprobes.id;
Regarding the above query, its only aliquot containers that can be used in 'kits'.
from Recent Questions - Stack Overflow https://ift.tt/2Pv6fQ6
https://ift.tt/3aGkARf
Comments
Post a Comment