Untitled

SELECT 
    s.serialnumber,
    s.product,
    s.size,
    i.location AS location,
    (s.location) originallocation,
     i.lastscandate,
    (s.lastscandate) originalscan,
    TIMESTAMPDIFF(DAY, s.lastscandate, i.lastscandate) lifespan
FROM
    (SELECT 
        serialnumber, product, size, location, lastscandate
    FROM
        shard.inventoryitemupdate
    WHERE
        location = 'PA Returns'
            AND lastscandate >= '2023-07-01'
            AND qtyonhand = 1) s
        JOIN
    inventoryitem i ON i.serialnumber = s.serialnumber
        AND i.location != 'PA Returns'
        AND i.previouslocation = 'PA Returns'
        AND i.qtyonhand = 1;
        
        SELECT 
    i.location AS location,
    (s.location) originallocation,
    AVG(TIMESTAMPDIFF(DAY,
        s.lastscandate,
        i.lastscandate)) AS avglifespan
FROM
    (SELECT 
        serialnumber, product, size, location, lastscandate
    FROM
        shard.inventoryitemupdate
    WHERE
        location = 'PA Returns'
            AND lastscandate >= '2023-07-01'
            AND qtyonhand = 1) s
        JOIN
    inventoryitem i ON i.serialnumber = s.serialnumber
        AND i.location != 'PA Returns'
        AND i.previouslocation = 'PA Returns'
        AND i.qtyonhand = 1