Turn biometric punch logs into daily attendance with SQLite

Four things raw punch data gets wrong

Before writing any query, look at a week of the export and you will almost certainly find all four:

1. 1Double taps. The employee presses a finger twice because the first beep was not heard. Two punches thirty seconds apart are not an entry and an exit.
2. 2Single punches. Someone forgot to punch out, or the device was down. First and last punch are the same, so hours come out as zero.
3. 3Midnight crossings. A 22:00 to 06:00 shift produces an evening punch on one date and a morning punch on the next. Grouping by calendar date splits one shift into two broken days.
4. 4Absentees. An employee who never came has no rows at all. A GROUP BY on the punch table can never report them.

The query below deals with each one in its own step, so you can check the output of each step on its own if a number looks wrong.

The attendance query

Everything here is synthetic: four employees, two days, one night-shift worker. It runs in any SQLite 3.25 or later (window functions are needed for LAG).

-- Synthetic data: one row per punch, as a biometric device export gives it.
CREATE TABLE punches (emp_id TEXT, punch_at TEXT);
INSERT INTO punches VALUES
 ('E101','2026-09-07 09:12:40'), ('E101','2026-09-07 09:13:05'), ('E101','2026-09-07 18:31:10'),
 ('E102','2026-09-07 10:05:00'), ('E102','2026-09-07 14:02:00'),
 ('E103','2026-09-07 09:40:00'),
 ('E104','2026-09-07 21:58:00'), ('E104','2026-09-08 06:04:00'),
 ('E101','2026-09-08 09:01:00'), ('E101','2026-09-08 17:59:00'),
 ('E103','2026-09-08 09:52:00'), ('E103','2026-09-08 18:40:00');
CREATE TABLE employees (emp_id TEXT PRIMARY KEY, shift TEXT, shift_start TEXT);
INSERT INTO employees VALUES
 ('E101','day','09:30'), ('E102','day','09:30'), ('E103','day','09:30'), ('E104','night','22:00');
CREATE TABLE work_days (work_date TEXT PRIMARY KEY);
INSERT INTO work_days VALUES ('2026-09-07'), ('2026-09-08');

WITH tagged AS (
  -- A night-shift punch before noon belongs to the previous evening's shift.
  SELECT p.emp_id, p.punch_at,
         CASE WHEN e.shift = 'night' AND time(p.punch_at) < '12:00:00'
              THEN date(p.punch_at, '-1 day') ELSE date(p.punch_at) END AS work_date
  FROM punches p JOIN employees e USING (emp_id)
),
deduped AS (
  -- Drop a punch within 2 minutes of the previous one: a double tap, not an exit.
  SELECT emp_id, work_date, punch_at FROM (
    SELECT emp_id, work_date, punch_at,
           (julianday(punch_at) - julianday(LAG(punch_at) OVER
              (PARTITION BY emp_id, work_date ORDER BY punch_at))) * 1440 AS gap_min
    FROM tagged)
  WHERE gap_min IS NULL OR gap_min > 2
),
daily AS (
  SELECT emp_id, work_date, COUNT(*) AS punches,
         MIN(punch_at) AS first_in, MAX(punch_at) AS last_out,
         ROUND((julianday(MAX(punch_at)) - julianday(MIN(punch_at))) * 24, 2) AS hours
  FROM deduped GROUP BY emp_id, work_date
)
SELECT e.emp_id, w.work_date,
       time(d.first_in) AS in_time,
       CASE WHEN d.punches > 1 THEN time(d.last_out) END AS out_time,
       CASE WHEN d.punches > 1 THEN d.hours END AS hours,
       CASE WHEN d.emp_id IS NULL THEN 'Absent'
            WHEN d.punches = 1   THEN 'Missing punch'
            WHEN d.hours >= 8    THEN 'Present'
            WHEN d.hours >= 4    THEN 'Half day'
            ELSE 'Short hours' END AS status,
       CASE WHEN e.shift = 'day' AND time(d.first_in) > time(e.shift_start, '+15 minutes')
            THEN 'Late' ELSE '' END AS late_flag
FROM employees e
CROSS JOIN work_days w
LEFT JOIN daily d ON d.emp_id = e.emp_id AND d.work_date = w.work_date
ORDER BY w.work_date, e.emp_id;

Reading the output

Notice E104 on 8 September. Their 06:04 punch has a calendar date of 8 September, yet it closed the 7 September shift, so 8 September has no punches and is correctly absent. Without the tagged step, 7 September would show a single punch and 8 September another one: two "missing punch" days for a full shift.

The policy numbers are yours, not the query's

Everything that looks like a rule in this query is really a parameter your HR policy has to supply:

• Half-day and full-day hours. Here 4 and 8. Some offices count 9 hours including lunch; some calculate on hours after deducting a break.
• Grace period. Here 15 minutes after shift start. Many policies also cap the number of late marks allowed per month before a deduction applies; that is a monthly count on top of this daily table.
• Double-tap window. Two minutes is a guess. Look at the gaps in your own data: genuine short exits like a tea break are rarely under five minutes, device retries are usually seconds.
• Night-shift cutoff. Noon works for a 22:00 to 06:00 shift. Rotating shifts need a shift roster table joined by date instead of a fixed shift column on the employee.

Write these values in one place, a small policy table or the top of the script, and print them on the report. When a manager disputes a half day, the first question is which threshold was used.

Missing punch is a status, not a guess

It is tempting to fill a missing out-punch with the shift end time. Do not do it in the report. A missing punch goes to HR for regularisation, usually through a request the employee raises and a manager approves. Keep those approvals in a separate table (emp_id, work_date, approved_in, approved_out, approved_by) and join them in as an override. The raw punches stay untouched, and anyone can see which days were adjusted and by whom.

Checks before you send it to payroll

• Count of rows equals employees multiplied by working days. If it does not, the cross join or the calendar is wrong.
• No employee has more than one row per date.
• Every punch in the raw table is either used or dropped as a double tap. Count both and make sure they add up to the raw count.
• Spot-check three people by hand against the device's own report for the same day.

This table is the input to the next job, turning a month of statuses into loss-of-pay days, which is covered in Convert a monthly attendance sheet into LOP days for payroll. If you want to become the person who writes queries like this rather than the person who fixes them by hand, SQL window functions and calendar joins are core practice in our Data Analyst programme.