Untitled

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