AKZO

SELECT 
    li.item,
    li.location,
    li.lot,
    li.on_hand_qty,
    li.inventory_sts,
    ((uom.length * uom.width * uom.height) * li.on_hand_qty) AS Item_Volume,
    uom.length,
    uom.width,
    uom.height,
    loc_dims.Location_Volume,
    loc_dims.length,
    loc_dims.width,
    loc_dims.height,
    ((SUM((uom.length * uom.width * uom.height) * li.on_hand_qty) / loc_dims.Location_Volume) * 100) AS Fullness_Percentage
FROM 
    v_location_inventory li
JOIN 
    v_item_unit_of_measure uom ON uom.item = li.item
JOIN 
    (
        SELECT 
            loc.Location,
            loc.location_type,
            (dims.length * dims.width * dims.height) AS Location_Volume,
            dims.length,
            dims.width,
            dims.height
        FROM 
            v_location loc
        JOIN 
            (
                SELECT 
                    location_type,
                    length,
                    width,
                    height
                FROM 
                    v_location_type
                WHERE 
                    location_type NOT IN ('AUTOSTORE', 'AUTOSTORE-PD', 'Autostore PD', 'CASH-BIN', 'Dock Door', 'PRE-STAGE', 'Staging', 'VIRTUAL', 'Yard Location')
            ) dims ON dims.location_type = loc.location_type
    ) loc_dims ON li.location = loc_dims.Location
WHERE 
    li.in_transit_qty = '0'
    AND li.allocated_qty = '0'
GROUP BY 
    li.item,
    li.location,
    li.lot,
    li.on_hand_qty,
    li.inventory_sts,
    uom.length,
    uom.width,
    uom.height,
    loc_dims.Location_Volume,
    loc_dims.length,
    loc_dims.width,
    loc_dims.height
HAVING 
    Fullness_Percentage IS NOT NULL
ORDER BY 
    Fullness_Percentage DESC;