如何通过SQL查询指定日期区间内学生的缺勤日期？

Let's break down the issues with your current stored procedure first, then fix it to get the student absence dates you need.

Problems with your existing code #

• You declared @StartDate and @EndDate but didn't actually use them—you hardcoded date values instead. The procedure should accept these as input parameters for reusability.
• The EXCEPT logic is backwards: right now it returns all attendance records outside your target date range, not the absences within it.
• Join condition mismatch: Your tblStudent table has a CustomerId field, but your join uses st.Code—that's almost certainly a typo, and should be st.CustomerId = att.CustomerId.
• Invalid field references: tblStudent only has CustomerId and Name, but you tried to use FirstName and LastName—that's a mistake (I'll assume you meant to use Name instead).

Solution 1: Absences = Dates in range where the student has NO attendance record #

This assumes an absence means a student didn't check in at all on that date. We'll generate all dates in your target range, pair them with every student, then identify which date-student combinations don't exist in the attendance table.

ALTER PROCEDURE spGetStudentAbsences
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON; -- Prevents extra metadata from cluttering results

    -- Step 1: Generate every date between the start and end date
    WITH DateRange AS (
        SELECT @StartDate AS DateVal
        UNION ALL
        SELECT DATEADD(DAY, 1, DateVal)
        FROM DateRange
        WHERE DateVal < @EndDate
    )

    -- Step 2: Match all students to all dates, then find missing attendance records
    SELECT
        st.CustomerId,
        st.Name,
        dr.DateVal AS AbsenceDate
    FROM tblStudent st
    CROSS JOIN DateRange dr -- Pair every student with every date in the range
    LEFT JOIN tblAttendance att
        ON st.CustomerId = att.CustomerId
        AND att.Date = dr.DateVal
    WHERE att.Id IS NULL -- No attendance record = absence
    ORDER BY st.CustomerId, dr.DateVal;

    -- Add this line if your date range exceeds 100 days to avoid recursion limits:
    -- OPTION (MAXRECURSION 0)
END
GO

Solution 2: Absences = Dates in range where attendance is marked as absent #

If your tblAttendance.Attendance field uses a specific value (like 0, 'Absent', etc.) to flag absences, use this simpler version:

ALTER PROCEDURE spGetStudentAbsences
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        st.CustomerId,
        st.Name,
        att.Date AS AbsenceDate
    FROM tblAttendance att
    INNER JOIN tblStudent st
        ON att.CustomerId = st.CustomerId
    WHERE
        att.Date BETWEEN @StartDate AND @EndDate
        AND att.Attendance = 'Absent'; -- Replace with your actual absence indicator (e.g., 0)
    ORDER BY st.CustomerId, att.Date;
END
GO

How to use the procedure #

Call it with your target date range like this:

EXEC spGetStudentAbsences '2020-02-15', '2020-02-25';

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