7217 track

SELECT 
    c.date,
    username,
    uph,
    bins,
    units,
    c.intime,
    firstscan,
    c.outtime,
    lastscan
FROM
    (SELECT 
        date, ADPEmployeeId, name, division, t.intime, t.outtime
    FROM
        (SELECT 
        DATE(timein) AS date,
            employeeid,
            MIN(timein) AS InTime,
            MAX(timeout) AS OutTime
    FROM
        timelog
    WHERE
        division = '7217'
            AND timein >= CURDATE()
    GROUP BY date , employeeid) t
    JOIN employee e ON e.id = t.employeeid) c
        JOIN
    (SELECT 
        DATE(lastscandate) AS date,
            username,
            SUBSTRING_INDEX(username, ' ', - 1) AS adpid,
            COUNT(DISTINCT serialnumber) / 8 AS UPH,
            COUNT(DISTINCT serialnumber) AS units,
            COUNT(DISTINCT bin) AS bins,
            MIN(lastscandate) AS firstscan,
            MAX(lastscandate) AS lastscan
    FROM
        shard.inventoryitemupdate
    WHERE
        lastscandate >= CURDATE()
    GROUP BY date , adpid
    HAVING adpid > 0) s ON s.adpid = c.ADPEmployeeId
        AND s.date = c.date;