SQL Queries

Employee Attendance#

When using SmartFace as an Access Control system, you will be most probably interested in a Employee Attendance data.

Attendance Log#

Script below gives you a raw history of detected occurrences in front of any camera. Basically a raw source of appearances of employees in front of particular gates:

CREATE VIEW core.attendance_log AS (
    SELECT
    mr.WatchlistMemberId AS employee_id,
    ISNULL(mr.WatchlistMemberFullName, mr.WatchlistMemberDisplayName) AS employee_name,
    mr.ProcessedAt AS event_at,
    mr.StreamId AS camera_id,
    s.Name AS camera_name
    FROM
    core.MatchResults AS mr
    LEFT JOIN core.Streams AS s ON mr.StreamId = s.Id
)

GO

SELECT * FROM core.attendance_log ORDER BY event_at DESC;

Attendance summary#

Sample above illustrates query how to get a daily check-in / check-out attendance for January 2024 only:

SELECT 
a.employee_id,
a.employee_name,
MIN(a.event_at) AS check_in,
MAX(a.event_at) AS check_out,
a.event_date
FROM (
    SELECT 
    *,
    FORMAT(event_at, 'yyyy-MM-dd') AS event_date
    FROM core.attendance_log
) AS a
WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY employee_id, employee_name, event_date
ORDER BY employee_id, event_date