SQL Query for Employee Attendance Report

Attendance reporting is one of the most common SQL tasks in HR and payroll systems. You need to know who showed up, who was late, who was absent, and what percentage of working days each employee actually attended. This guide walks through a realistic attendance schema and builds up the queries you need — daily status, monthly percentage, late arrivals, and absentee lists — using MySQL and PostgreSQL syntax.

The Schema: employees and attendance_logs

A minimal but realistic attendance system needs two tables: one for employee records and one that logs each day's check-in and check-out event.

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL,
  shift_start TIME NOT NULL DEFAULT '09:00:00'
);

CREATE TABLE attendance_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  employee_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  check_in DATETIME NULL,
  check_out DATETIME NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'present', -- present, absent, half_day, leave
  FOREIGN KEY (employee_id) REFERENCES employees(id)
);

One row per employee per calendar day is the simplest design: if the employee did not show up, a row still exists with status = 'absent' and NULL check-in/out times. This makes attendance percentage calculations much easier than trying to infer absence from missing rows.

Daily Attendance Report

A daily report lists every employee's status for a given date, along with how many hours they worked if they checked out.

-- MySQL / PostgreSQL compatible
SELECT
  e.full_name,
  e.department,
  a.status,
  a.check_in,
  a.check_out,
  TIMESTAMPDIFF(MINUTE, a.check_in, a.check_out) / 60.0 AS hours_worked  -- MySQL
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.attendance_date = '2026-07-10'
ORDER BY e.department, e.full_name;

-- PostgreSQL equivalent for hours worked:
-- EXTRACT(EPOCH FROM (a.check_out - a.check_in)) / 3600 AS hours_worked

Monthly Attendance Percentage

Attendance percentage is the ratio of days present to total logged working days in the month. Use a conditional SUM with CASE to count "present" days, and COUNT(*) for the total days on record.

SELECT
  e.id AS employee_id,
  e.full_name,
  COUNT(*) AS total_days,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) AS days_present,
  ROUND(
    SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
    2
  ) AS attendance_percentage
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.attendance_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY e.id, e.full_name
ORDER BY attendance_percentage ASC;

To flag employees who fall below a required attendance threshold, add a HAVING clause after the GROUP BY — HAVING filters on the aggregated result, unlike WHERE which filters individual rows before aggregation:

SELECT
  e.full_name,
  ROUND(SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS attendance_percentage
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.attendance_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY e.id, e.full_name
HAVING SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) < 75
ORDER BY attendance_percentage ASC;

Late Arrivals — Check-In After a Threshold Time

To find late arrivals, compare only the time portion of the check-in timestamp against the shift start time plus a grace period. MySQL uses TIME() to extract the time component; PostgreSQL uses a cast to ::time.

-- MySQL: employees who checked in after 9:15 AM (15-minute grace period)
SELECT
  e.full_name,
  a.attendance_date,
  a.check_in,
  TIME(a.check_in) AS check_in_time
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.status = 'present'
  AND TIME(a.check_in) > '09:15:00'
ORDER BY a.attendance_date DESC, check_in_time DESC;

-- PostgreSQL equivalent
SELECT
  e.full_name,
  a.attendance_date,
  a.check_in,
  a.check_in::time AS check_in_time
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.status = 'present'
  AND a.check_in::time > TIME '09:15:00'
ORDER BY a.attendance_date DESC, check_in_time DESC;

For a per-employee shift start time stored on the employees table instead of a fixed threshold, join against e.shift_start and add the grace period with interval arithmetic:

-- MySQL: compare against each employee's own shift start + 15 minutes
SELECT e.full_name, a.attendance_date, TIME(a.check_in) AS check_in_time
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.status = 'present'
  AND TIME(a.check_in) > ADDTIME(e.shift_start, '00:15:00');

Absentee Report

List every employee marked absent on a given date, or count total absences per employee over a date range to spot attendance issues early.

-- Absentees for a single day
SELECT e.full_name, e.department
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.attendance_date = '2026-07-10'
  AND a.status = 'absent'
ORDER BY e.department, e.full_name;

-- Employees with more than 3 absences this month
SELECT
  e.full_name,
  COUNT(*) AS absent_days
FROM employees e
JOIN attendance_logs a ON a.employee_id = e.id
WHERE a.status = 'absent'
  AND a.attendance_date BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY e.id, e.full_name
HAVING COUNT(*) > 3
ORDER BY absent_days DESC;

Common attendance metrics worth tracking beyond raw presence:

• Attendance percentage — present days divided by total working days
• Late arrival count — how many times check-in exceeded the grace period
• Average hours worked — average of check-out minus check-in per day
• Consecutive absences — often calculated with window functions to detect streaks
• Half-day count — rows where status is 'half_day', useful for payroll deductions

Frequently Asked Questions