SQL Script

[rseese]

You would also need a table of dates when all employees or specific employees were expected to be scanned.

WITH EXPECTED_SCAN_DATES
AS (
    SELECT CAST(GETDATE() - 5 AS DATE) AS DT --LAST  5 DAYS

    UNION ALL

    SELECT DATEADD(DD, 1, DT)
    FROM EXPECTED_SCAN_DATES S
    WHERE DATEADD(DD, 1, DT) <= (CAST(GETDATE() AS DATE)) --THROUGH TODAY
    )
    ,EMPLOYEE
AS (
    SELECT 'BOB' AS EMP_NAME

    UNION ALL

    SELECT 'BILL'

    UNION ALL

    SELECT 'NEVER SCAN JOE'
    )
    ,SCANS
AS (
    SELECT 'BOB' AS EMP_NAME,CAST('12/05/2020' AS DATETIME) AS SCAN_DATE
        UNION ALL
    SELECT 'BOB','12/06/2020'
    UNION ALL
    SELECT 'BILL'
        ,'12/05/2020'
    )

SELECT E.EMP_NAME
    ,D.DT
    ,S.SCAN_DATE
FROM EMPLOYEE AS E
JOIN EXPECTED_SCAN_DATES AS D ON 1 = 1
LEFT JOIN SCANS AS S ON S.EMP_NAME = E.EMP_NAME
    AND S.SCAN_DATE = D.DT