Untitled

SELECT 
    DATE(logdate) AS logdate,
    DATE(MIN(lastscandate)) AS lastscandate,
    FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(j1.location, '-', 2),
                    '-',
                    - 1),
            (SELECT 
                    datavalue
                FROM
                    revolvedata
                WHERE
                    id = 'pickpath-10')) AS path,
    'PA' AS WH,
    username,
    CONCAT('s', j2.serialnumber) AS serial,
    product,
    size,
    (SELECT 
            commoncolor
        FROM
            product
        WHERE
            code = product) AS color,
    IF(SUBSTRING_INDEX(j2.location, '-', 2) <> SUBSTRING_INDEX(j1.location, '-', 2),
        1,
        0) AS discrepancy,
    IF(SUBSTRING_INDEX(j2.location, '-', 2) <> SUBSTRING_INDEX(j1.location, '-', 2),
        CONCAT(j1.location,
                ' (Discrepancy: ',
                j2.location,
                ')'),
        j1.location) AS location_,
    j1.bin,
    COUNT(*) + scanned AS total_serials,
    scanned,
    COUNT(*) AS missing
FROM
    (SELECT 
        t1.*
    FROM
        (SELECT 
        TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1))) AS username,
            location,
            bin,
            MAX(lastscandate) AS logdate,
            COUNT(*) AS scanned
    FROM
        inventoryitem
    WHERE
        location LIKE '10-%' AND bin != ''
            AND TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1))) IN ('James Allen Consentino' , 'Luisa Jon Felix',
        'Jonathan Mejia',
        'Rafael Ascension',
        'Merly Liriano 9169')
            AND qtyonhand > 0
            AND serialnumber NOT IN (SELECT 
                serialnumber
            FROM
                inventoryupdate_extend
            WHERE
                createdate > lastscandate)
    GROUP BY bin , username
    ORDER BY bin , logdate DESC) t1
    GROUP BY bin) j1
        JOIN
    inventoryitem j2 ON j1.bin = j2.bin
WHERE
    qtyonhand > 0 AND lastscandate < logdate
        AND (TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1),
                        ',',
                        - 1))) <> username
        OR lastscandate < DATE(logdate))
        AND serialnumber NOT IN (SELECT 
            serialnumber
        FROM
            inventoryupdate_extend
        WHERE
            createdate > lastscandate)
GROUP BY bin , username
HAVING path > 0 
UNION SELECT 
    DATE(logdate) AS logdate,
    DATE(MIN(lastscandate)) AS lastscandate,
    FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX(j1.location, '-', 2),
                    '-',
                    - 1),
            (SELECT 
                    datavalue
                FROM
                    revolvedata
                WHERE
                    id = 'pickpath-10')) AS path,
    'PA' AS WH,
    username,
    CONCAT('s', j2.serialnumber) AS serial,
    product,
    size,
    (SELECT 
            commoncolor
        FROM
            product
        WHERE
            code = product) AS color,
    0 AS discrepancy,
    j1.location AS location_,
    bin,
    COUNT(*) + scanned AS total_serials,
    scanned,
    COUNT(*) AS missing
FROM
    (SELECT 
        t1.*
    FROM
        (SELECT 
        TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1))) AS username,
            location,
            MAX(lastscandate) AS logdate,
            COUNT(*) AS scanned
    FROM
        inventoryitem
    WHERE
        location LIKE '10-%' AND bin = ''
            AND TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1))) IN ('James Allen Consentino' , 'Luisa Jon Felix',
        'Jonathan Mejia',
        'Rafael Ascension',
        'Merly Liriano')
            AND qtyonhand > 0
            AND serialnumber NOT IN (SELECT 
                serialnumber
            FROM
                inventoryupdate_extend
            WHERE
                createdate > lastscandate)
    GROUP BY location , username
    ORDER BY location , logdate DESC) t1
    GROUP BY location) j1
        JOIN
    inventoryitem j2 ON j1.location = j2.location
WHERE
    qtyonhand > 0 AND lastscandate < logdate
        AND bin = ''
        AND (TRIM(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1),
                        ',',
                        - 1))) <> username
        OR lastscandate < DATE(logdate))
        AND serialnumber NOT IN (SELECT 
            serialnumber
        FROM
            inventoryupdate_extend
        WHERE
            createdate > lastscandate)
GROUP BY location_ , username
HAVING path > 0
ORDER BY path
LIMIT 100000;