考勤打卡时间SQL查询：补全日期区间内缺失记录

Got it, let's work through this attendance SQL problem together. I'll break down exactly how to meet both of your requirements, step by step, with clear code examples.

Step 1: Generate Your Target Date Range #

First, we need to create a complete list of dates in your specified interval—this is how we'll ensure we have rows even for days with no attendance. A recursive CTE is a clean, portable way to do this (adjust date functions slightly if you're using MySQL/Oracle instead of SQL Server/PostgreSQL):

WITH date_range AS (
    -- Swap in your actual start and end dates here
    SELECT CAST('2024-01-01' AS DATE) AS target_date
    UNION ALL
    SELECT DATEADD(DAY, 1, target_date)
    FROM date_range
    WHERE target_date < CAST('2024-01-31' AS DATE)
),

Step 2: Capture All Unique Employees (If Tracking Per-Employee Attendance) #

If you need to see every employee's attendance (or lack thereof) for every date, we'll grab all unique employee IDs from both tables to make sure we don't miss anyone:

unique_employees AS (
    SELECT EMP_ID FROM TIMEIN
    UNION
    SELECT EMP_ID FROM TIMEOUT
),

Step 3: Create a Base of All Employee-Date Combinations #

This cross join generates a row for every employee on every date in your range. This is the foundation that ensures we have rows for days with zero attendance:

employee_date_base AS (
    SELECT u.EMP_ID, d.target_date
    FROM unique_employees u
    CROSS JOIN date_range d
),

Step 4: Join Attendance Tables with Full Outer Join #

We use a full outer join between TIMEIN and TIMEOUT to handle cases where a record exists in one table but not the other. This automatically sets IINDX or OINDX to NULL when there's no matching entry, which meets your first requirement:

attendance_matches AS (
    SELECT
        t_in.IINDX,
        t_out.OINDX,
        t_in.EMP_ID,
        t_in.IN_DATE,
        t_in.IN_TIME,
        t_out.OUT_TIME
    FROM TIMEIN t_in
    FULL OUTER JOIN TIMEOUT t_out
        ON t_in.EMP_ID = t_out.EMP_ID
        AND t_in.IN_DATE = t_out.OUT_DATE
)

Step 5: Combine Base Rows with Attendance Data #

Finally, we left join our employee-date base to the attendance matches. This preserves all the base rows (even for days with no attendance) and fills in the attendance data where it exists:

SELECT
    em.EMP_ID,
    em.target_date AS attendance_date,
    am.IINDX,
    am.OINDX,
    am.IN_TIME,
    am.OUT_TIME
FROM employee_date_base em
LEFT JOIN attendance_matches am
    ON em.EMP_ID = am.EMP_ID
    AND em.target_date = COALESCE(am.IN_DATE, am.OUT_DATE)
ORDER BY em.EMP_ID, em.target_date;

Quick Adjustments for Different Use Cases: #

• If you don't need per-employee rows: Skip the unique_employees and employee_date_base CTEs. Instead, left join the date_range directly to attendance_matches using target_date = COALESCE(am.IN_DATE, am.OUT_DATE). This will give you a single row per date, with NULLs if no one attended that day.
• Dialect-specific date functions: For MySQL, use DATE_ADD(target_date, INTERVAL 1 DAY) instead of DATEADD. For PostgreSQL, use target_date + INTERVAL '1 day'.
• Additional fields: Add any other columns from TIMEIN or TIMEOUT to the select list as needed—they'll automatically be NULL when there's no matching record.

How This Checks Both Boxes: #

1. NULL for mismatched IINDX/OINDX: The full outer join ensures that if a TIMEOUT record has no corresponding TIMEIN entry, IINDX is NULL, and vice versa.
2. Rows for dates with no attendance: The cross join (or date range alone) creates a base row for every date (and employee), and the left join keeps those rows even when there's no attendance data, with all attendance fields set to NULL.

内容的提问来源于stack exchange，提问作者Hussain Zagzoug