Untitled

mail@pastecode.io avatar
unknown
mysql
7 months ago
924 B
4
Indexable
Never
SELECT 
    items AS serials, bin, location, scandate AS lastscandate
FROM
    (SELECT 
        FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(location, '-', 2), '-', - 1), (SELECT 
                    datavalue
                FROM
                    revolvedata
                WHERE
                    id = 'pickpath-10')) AS path,
            MAX(DATE(lastscandate)) AS scandate,
            COUNT(*) AS items,
            location,
            bin,
            SUM((SELECT 
                    (sellableqty)
                FROM
                    inventory
                WHERE
                    product = t1.product AND option1 = size)) AS sellableqty
    FROM
        inventoryitem t1
    WHERE
        location LIKE '10-%' AND qtyonhand = 1
    GROUP BY location , bin
    HAVING path > 0
        AND scandate <= DATE_SUB(CURRENT_DATE, INTERVAL 25 DAY)
        AND sellableqty > 0) j1
ORDER BY path
LIMIT 100000;