Objective: Query for computed location capacity info. Surfaces `total_capacity`, `occupied_capacity`, `available_capacity` and `is_over_capacity` fields for all locations with capacity configured. Additional filters or sorts can be added as desired.
This query doesn't work as is if any of the relevant tables (location, plate, box, container) have no data. For example, there are no inventory items of those types.
Replace `organization` with your organization name.
%%sql
-- Select locations with capacity configured to start with
-- Additional filters on the location table can be added here
WITH capacity_locations as (
SELECT id, total_capacity, allowed_inventory_schema_ids, allows_non_location_children
from organization.location as loc
where allows_non_location_children is true and total_capacity is not null
),
-- Build capacity view with the total/occupied/available columns (simulating
-- what we'll have eventually when those columns are added)
capacity_view as (
Select capacity_data.loc_id, capacity_data.total_capacity,
(capacity_data.child_inventory_count * 100.0 / capacity_data.total_capacity) as occupied_percent,
capacity_data.child_inventory_count as occupied_capacity,
(case when capacity_data.total_capacity - capacity_data.child_inventory_count > 0
then capacity_data.total_capacity - capacity_data.child_inventory_count
else 0 end) as available_capacity,
capacity_data.child_inventory_count > capacity_data.total_capacity as is_over_capacity
from (
select location.id as loc_id,
location.total_capacity,
count (distinct(plate.id)) + count(distinct(box.id)) + count(distinct(container.id)) as child_inventory_count
from capacity_locations as location
Left join organization.plate as plate on plate.location_id = location.id AND
(location.allowed_inventory_schema_ids is null or location.allowed_inventory_schema_ids ? plate.schema_id)
Left join organization.container as container on container.location_id = location.id AND
(location.allowed_inventory_schema_ids is null or location.allowed_inventory_schema_ids ? container.schema_id)
Left join organization.box as box on box.location_id = location.id AND
(location.allowed_inventory_schema_ids is null or location.allowed_inventory_schema_ids ? box.schema_id)
group by location.id, location.total_capacity) as capacity_data
)
-- Here is where you can modify which columns are shown or add sorts on them
select * from capacity_view
Objective: Query for computed grid capacity info. Surfaces `total_capacity`, `occupied_capacity`, `available_capacity` and `is_over_capacity` fields for all boxes and matrix plates. Additional filters or sorts can be added as desired. Fixed plates have a null capacity and are excluded.
This query will not work as is if any of the relevant tables (plate, box, container) have no data, i.e. there are no inventory items of those types.
Replace `organization` with your organization name.
%%sql
-- Select boxes/matrix plates to start with & select relevant fields
-- Additional filters on the box or plate table can be added here
WITH capacity_grids as (
SELECT p.id as grid_id, total_capacity from organization.plate as p
where total_capacity is not null
UNION
SELECT b.id as grid_id, total_capacity from organization.box as b
),
-- Build capacity view with the total/occupied/available columns (simulating
-- what we'll have eventually when those columns are added)
capacity_view as (
Select capacity_data.grid_id,
capacity_data.total_capacity,
(capacity_data.containers * 100.0 / capacity_data.total_capacity) as occupied_percent,
capacity_data.containers as occupied_capacity,
capacity_data.total_capacity - capacity_data.containers as available_capacity
from (
select grid.grid_id as grid_id,
grid.total_capacity,
count (distinct(container.id)) as containers
from capacity_grids as grid
Left join organization.container as container on container.plate_id = grid.grid_id OR container.box_id = grid.grid_id
group by grid.grid_id, grid.total_capacity) as capacity_data
)
-- Here is where you can modify which columns are shown or add sorts on them
select * from capacity_view