Write a SQL query that can view attendance for approximately 100 employees in the system.

Question

Assisting AI · Accepted Answer

Assuming you have two tables, Employees(employee_id, name, ...) and Attendance(att_id, employee_id, date, status), you can retrieve the attendance records for the first 100 employees with:

```sql
SELECT e.employee_id,
       e.name,
       a.date,
       a.status
FROM   Employees e
JOIN   Attendance a
       ON e.employee_id = a.employee_id
WHERE  e.employee_id IN (
        SELECT employee_id
        FROM   Employees
        ORDER BY employee_id
        LIMIT 100
      );
```

If you want a more efficient approach, use a window function to rank employees and then filter:

```sql
WITH ranked AS (
  SELECT employee_id,
         ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
  FROM   Employees
)
SELECT e.employee_id,
       e.name,
       a.date,
       a.status
FROM   ranked r
JOIN   Employees e
       ON e.employee_id = r.employee_id
JOIN   Attendance a
       ON a.employee_id = e.employee_id
WHERE  r.rn <= 100;
```

Both queries return the attendance rows for the first 100 employees. Adjust the ORDER BY clause if you need a different selection criterion (e.g., most recent hires).

Write a SQL query that can view attendance for approximately 100 employees in the system.

💡 Model Answer

🎤 Get questions like this answered in real-time