SQL Server：根据指定月份数获取人员历史状态值的实现方案

Hey there! I get why this was tricky—those missing months can throw off straightforward joins. Let's break down how to get your desired PreviousStatus column, even with gaps in the MonthDate records.

Core Idea #

For each record, we need to:

1. Convert the MonthDate (like 2017-12) into a proper date value (we'll use the first day of the month for consistency).
2. Calculate the target date by subtracting MonthsAgoSinceLastCheck months from the current record's date.
3. Find the most recent record for the same person that falls on or before this target date—its Status is our PreviousStatus.

SQL Implementation (Works in Most Databases) #

Here's a robust solution using CTEs and window functions to handle the missing months:

WITH formatted_data AS (
    -- Convert MonthDate to a full date (first day of the month)
    SELECT
        ID,
        MonthDate,
        PersonID,
        Name,
        Status,
        -- Replace '-' with NULL for easier date calculations
        CASE WHEN MonthsAgoSinceLastCheck = '-' THEN NULL ELSE CAST(MonthsAgoSinceLastCheck AS INT) END AS MonthsAgo,
        CAST(MonthDate || '-01' AS DATE) AS actual_date
    FROM your_table_name
),
ranked_previous_records AS (
    SELECT
        fd.ID,
        fd.MonthDate,
        fd.PersonID,
        fd.Name,
        fd.Status,
        fd.MonthsAgo,
        prev.Status AS PreviousStatus,
        -- Rank matching records by date (newest first)
        ROW_NUMBER() OVER (
            PARTITION BY fd.ID
            ORDER BY prev.actual_date DESC
        ) AS record_rank
    FROM formatted_data fd
    LEFT JOIN formatted_data prev
        ON fd.PersonID = prev.PersonID
        -- Only match records on or before the target date
        AND prev.actual_date <= DATEADD(month, -fd.MonthsAgo, fd.actual_date)
)
SELECT
    ID,
    MonthDate,
    PersonID,
    Name,
    Status,
    -- Convert NULL back to '-' for consistency
    CASE WHEN MonthsAgo IS NULL THEN '-' ELSE CAST(MonthsAgo AS VARCHAR) END AS MonthsAgoSinceLastCheck,
    -- Use '-' if no matching record exists
    COALESCE(PreviousStatus, '-') AS PreviousStatus
FROM ranked_previous_records
-- Only keep the newest matching historical record
WHERE record_rank = 1 OR record_rank IS NULL
ORDER BY ID;

How This Works #

1. formatted_data CTE:

   • Converts MonthDate to a valid date (e.g., 2017-12 becomes 2017-12-01).
   • Replaces the - in MonthsAgoSinceLastCheck with NULL to avoid calculation errors.

2. ranked_previous_records CTE:

   • Joins each record to all older records for the same person that fall on or before the target date (current date minus MonthsAgo).
   • Uses ROW_NUMBER() to rank these matching records by date—so the most recent one (closest to the target date) gets record_rank = 1.

3. Final Select:

   • Filters for only the top-ranked historical record (or no record if MonthsAgo is NULL).
   • Converts values back to the original format (e.g., NULL becomes - for MonthsAgoSinceLastCheck).

Testing with Your Sample Data #

If you run this against your sample table, you'll get exactly the expected output:

• For Jack's 2018-02 record (ID=3), the target date is 2018-02-01 minus 2 months = 2017-12-01, which matches his 2017-12 record (Status=Ill).
• For Bill's 2017-05 record (ID=7), the target date is 2017-05-01 minus 2 months = 2017-03-01, which matches his 2017-03 record (Status=Healthy).

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