如何在Laravel中生成指定日期区间内所有员工的缺勤报告？

Got it, let's tackle how to generate that absence report for all employees between two specified dates. Here's a practical, step-by-step SQL solution tailored to your attendance table structure:

The core challenge here is identifying every employee-date pair in your target range that doesn't exist in the attendance table (those are the absences). We'll break this into manageable parts:

Step 1: Define Your Date Range & Generate All Dates #

First, we'll create a list of every date between your start and end date using a recursive CTE (works for SQL Server; adjust syntax if using PostgreSQL/MySQL):

DECLARE @StartDate DATE = '2024-01-01'; -- Replace with your start date
DECLARE @EndDate DATE = '2024-01-15';   -- Replace with your end date

WITH DateRange AS (
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRange
    WHERE DateValue < @EndDate
),

Step 2: Get the Full List of Employees #

Next, pull all unique employees from your attendance table (if you have a dedicated Employees table, use that instead—it's more reliable):

AllEmployees AS (
    SELECT DISTINCT id AS EmployeeId, name AS EmployeeName
    FROM attendance_table
)

Step 3: Find Missing Attendance Entries (Absences) #

Cross-join the date range with employees to get every possible employee-date pair, then left-join with the attendance table to filter out dates where the employee was present:

SELECT 
    ae.EmployeeId,
    ae.EmployeeName,
    dr.DateValue AS AbsenceDate
FROM DateRange dr
CROSS JOIN AllEmployees ae
LEFT JOIN attendance_table att
    ON ae.EmployeeId = att.id 
    AND dr.DateValue = att.date
WHERE att.id IS NULL -- Filter out dates where attendance was recorded
ORDER BY ae.EmployeeName, dr.DateValue;

Key Notes: #

• Dedicated Employees Table: If you have a separate Employees table (best practice), replace the AllEmployees CTE with SELECT EmployeeId, EmployeeName FROM Employees.
• Database-Specific Adjustments: For PostgreSQL, use generate_series to create the date range. For MySQL, use a recursive CTE or a pre-built numbers table.
• Output: The result will list every employee and every date in your range where they have no entry in the attendance table.

Example Output: #

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