Untitled

SELECT 
    s.serialnumber,
    s.product,
    s.size,
    s.location,
    s.lastscandate,
    i.lastscandate,
    avg(TIMESTAMPDIFF(DAY, s.lastscandate, i.lastscandate)) AS shelflife
FROM
    (SELECT 
        serialnumber, product, size, location, lastscandate
    FROM
        shard.inventoryitemupdate
    WHERE
        previouslocation = 'trucktowarehouse2'
            AND lastscandate >= '2023-01-01'
            AND qtyonhand = 1) s
        JOIN
    inventoryitem i ON i.serialnumber = s.serialnumber
    AND i.previouslocation = 'trucktowarehouse2'
        AND i.qtyonhand = 1