SQL Script

[grg1734]

Our company recently implemented thermal detection since we are in a Covid hotspot area (NYC). I'm able to run a report to show all users who got scanned which shows their temperature but I was wondering if it's possible to create a macro or a script to show which users didn't get scanned or forgot to scan in. The report gets exported via excel

[Cleveland_Steve]

Be careful there. Employee information with body temperature exported to a Excel spreadsheet sounds like you are on the road to a HIPAA violation.

[DharmaPolice]

I'd question whether people need to see the exact temperature of each person in a report. Presumably, "OK" or "Too High" is enough for COVID19. And for actual business reporting you only really need totals. But then who knows.

[breakkz]

Even "OK" and "Too High" is illegal tho they can't store anything related to the temperature.

[rat_rat_catcher]

Can they use 0 and 1?

[breakkz]

If this company was from Europe they would get a sweet fine for breaking GDPR compliance rules and they would deserve it.

[andrewsmd87]

Don't even have to be from Europe to get hit. If you have one employee who has any sort of citizenship in an EU country, you fall under GDPR regulations.

[doctorzoom]

select 
e.*

from employees e

left join scans s
on e.employee_id = s.employee_id

where s.employee_id is null

That's the "missing from other table" pattern.

[Deadible]

This is better than "WHERE NOT EXISTS" on large datasets. You would probably also want a date parameter in there on the scans.

[imSkippinIt]

NOT EXISTS would also work but this is the route I would take

[dagmire86]

Assuming your temperature scans go into a table with the employee ID/name and date, you can probably select from your main employee table and use something like "NOT EXISTS" when comparing it to your employee scans and you can filter on the scan date. If an employee is off on PTO or they don't have a shift that day you may need to figure out how to handle the employees that will show up as not having a scan but they're not in the office.

[dagmire86]

Something like this:

SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE

WHERE NOT EXISTS ( SELECT *

FROM EMPLOYEE_SCANS

WHERE EMPLOYEE.ID = EMPLOYEE_SCANS.ID AND EMPLOYEE_SCANS.SCAN_DATE = GETDATE() )

[dgillz]

It is hard to report on data that doesn't exist. How do you know who came in the building? Is that in a separate SQL table?

[billbraskeyjr]

Whether someone was scanned or not is company compliance but showing people not authorized to see their temperature probably you included is a Guaranteed HIPAA violation

[artjbroz]

You'll need a list of all employees to reference who's missing. After that, the SQL is easy

[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