编写按最近日期筛选数据并以PlanID为决胜器的SQL查询脚本

Got it, let's work through this problem step by step. First, let's formalize the table structure since your create statement got cut off—this is what I'm assuming (adjust if your actual schema has slight differences):

CREATE TABLE ClosestDate (
    CareID int NOT NULL,
    PlanID int NOT NULL,
    PerformanceStatus int NOT NULL,
    DiagnosisDate date NULL,
    AgreedDate date NULL,
    MDTDate date NULL
);

Core Logic Breakdown #

We need to:

• For each CareID, find the record where either AgreedDate or MDTDate is closest to DiagnosisDate (doesn't matter if it's before or after)
• If multiple records have the same closest date difference, pick the one with the largest PlanID

SQL Query Implementation #

Using a CTE (Common Table Expression) with window functions is the cleanest way to handle this ranking logic:

WITH ranked_plans AS (
    SELECT 
        CareID,
        PlanID,
        PerformanceStatus,
        DiagnosisDate,
        AgreedDate,
        MDTDate,
        -- Calculate the smallest absolute difference between DiagnosisDate and either of the two dates
        LEAST(
            ABS(DATEDIFF(day, DiagnosisDate, AgreedDate)),
            ABS(DATEDIFF(day, DiagnosisDate, MDTDate))
        ) AS closest_day_diff,
        -- Rank records per CareID: prioritize smallest date difference first, then largest PlanID
        ROW_NUMBER() OVER (
            PARTITION BY CareID 
            ORDER BY 
                LEAST(
                    ABS(DATEDIFF(day, DiagnosisDate, AgreedDate)),
                    ABS(DATEDIFF(day, DiagnosisDate, MDTDate))
                ) ASC,
                PlanID DESC
        ) AS record_rank
    FROM ClosestDate
    -- Optional: Filter out rows with null dates if your data allows it (avoids invalid date difference calculations)
    WHERE DiagnosisDate IS NOT NULL 
      AND AgreedDate IS NOT NULL 
      AND MDTDate IS NOT NULL
)
SELECT 
    CareID,
    PlanID,
    PerformanceStatus,
    DiagnosisDate,
    AgreedDate,
    MDTDate
FROM ranked_plans
WHERE record_rank = 1;

How This Works #

1. Date Difference Calculation: ABS(DATEDIFF(...)) gives us the absolute number of days between two dates—this ignores whether the date is before or after DiagnosisDate. LEAST() picks the smaller of the two differences (from AgreedDate and MDTDate) for each record.
2. Ranking: ROW_NUMBER() partitions the data by CareID, so we handle each group separately. We sort first by the smallest date difference, then by the largest PlanID (so ties for closest date go to the higher PlanID).
3. Selecting the Winner: By filtering where record_rank = 1, we get exactly one row per CareID that meets all your criteria.

Notes #

• If your data has null values for DiagnosisDate, AgreedDate, or MDTDate, you can adjust the WHERE clause or use COALESCE to handle them (e.g., if a date is null, you might want to exclude that date from the comparison).
• This uses SQL Server syntax for DATEDIFF—if you're using a different database (like PostgreSQL, MySQL), you'll need to adjust the date difference function (e.g., ABS(DATE_PART('day', DiagnosisDate - AgreedDate)) for PostgreSQL).

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