基于Microsoft SQL Server查找缺勤记录的健康证明日期缺口

Alright, let's break down how to solve this problem step by step. We need to spot missing FIT note coverage for absences that last 7 days or longer, focusing specifically on the window from 7 days after the absence starts up to its end date. Here's a practical, SQL Server-tailored approach:

Step 1: Filter Eligible Absences #

First, we'll isolate the absences that match your criteria: non-null end date and total duration of 7+ days. We'll also calculate the exact date range we need to check for coverage gaps.

Step 2: Generate Date Range for Each Absence #

Next, we'll create a list of every date in the target window (start +7 days to end date) for each eligible absence. Recursive CTEs work well for small to medium datasets, while a pre-existing numbers/dates dimension table is better for large volumes of data.

Step 3: Identify Coverage Gaps #

Finally, we'll compare our generated dates against the FIT note records to find dates that aren't covered by any FIT note.

Option 1: Using Recursive CTE (Great for Small to Medium Datasets) #

WITH EligibleAbsences AS (
    -- Filter absences that are 7+ days long with non-null end date
    SELECT 
        AbsenceID,
        DATEADD(day, 7, [Start Date]) AS CoverageStart,
        CAST([End Date] AS DATE) AS CoverageEnd -- Ensure we work with date-only values
    FROM AbsenceHistory
    WHERE 
        [End Date] IS NOT NULL
        AND DATEDIFF(day, [Start Date], [End Date]) >= 7
),
DateRange AS (
    -- Recursively generate every date in the coverage window
    SELECT 
        AbsenceID,
        CoverageStart AS GapDate
    FROM EligibleAbsences
    UNION ALL
    SELECT 
        dr.AbsenceID,
        DATEADD(day, 1, dr.GapDate)
    FROM DateRange dr
    JOIN EligibleAbsences ea ON dr.AbsenceID = ea.AbsenceID
    WHERE dr.GapDate < ea.CoverageEnd
)
-- Find dates with no matching FIT note coverage
SELECT 
    dr.AbsenceID,
    dr.GapDate
FROM DateRange dr
LEFT JOIN FITNoteHistory fn 
    ON dr.AbsenceID = fn.AbsenceID
    -- Adjust this condition to match your FITNoteHistory structure:
    -- For date-range coverage:
    AND dr.GapDate BETWEEN fn.[Covered Start Date] AND fn.[Covered End Date]
    -- For single-date coverage:
    -- AND dr.GapDate = fn.[Covered Date]
WHERE fn.AbsenceID IS NULL -- No coverage found for this date
ORDER BY dr.AbsenceID, dr.GapDate;

Option 2: Using a Numbers/Dates Dimension Table (Better for Large Datasets) #

If you have a pre-existing Numbers table (with a sequential integer column Number starting at 1), this method is more efficient for large date ranges:

WITH EligibleAbsences AS (
    SELECT 
        AbsenceID,
        DATEADD(day, 7, [Start Date]) AS CoverageStart,
        CAST([End Date] AS DATE) AS CoverageEnd,
        -- Calculate total days in the coverage window
        DATEDIFF(day, DATEADD(day, 7, [Start Date]), [End Date]) + 1 AS TotalDays
    FROM AbsenceHistory
    WHERE 
        [End Date] IS NOT NULL
        AND DATEDIFF(day, [Start Date], [End Date]) >= 7
),
DateRange AS (
    -- Generate dates using the Numbers table
    SELECT 
        ea.AbsenceID,
        DATEADD(day, n.Number - 1, ea.CoverageStart) AS GapDate
    FROM EligibleAbsences ea
    JOIN Numbers n ON n.Number <= ea.TotalDays
)
SELECT 
    dr.AbsenceID,
    dr.GapDate
FROM DateRange dr
LEFT JOIN FITNoteHistory fn 
    ON dr.AbsenceID = fn.AbsenceID
    AND dr.GapDate BETWEEN fn.[Covered Start Date] AND fn.[Covered End Date]
WHERE fn.AbsenceID IS NULL
ORDER BY dr.AbsenceID, dr.GapDate;

Key Notes to Keep in Mind #

• Date Type Handling: If your date columns include time values, use CAST([Date Column] AS DATE) to avoid mismatches from time components.
• FIT Note Structure: Tweak the join condition in the final query to match how your FITNoteHistory stores coverage (either date ranges or single dates).
• Testing: Validate with a known absence record first to ensure the date range and gap detection work as expected.

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