基于Row_num连续序列对循环执行SQL，实现账户周度状态变化统计的技术需求

Alright, let's fix this up properly. The key here is that each account's status for a week depends on its presence in the previous week (to identify new accounts) and the next week (to identify expired accounts)—especially since Row_num=1 represents the most recent week. Here's a clean solution that covers all cases:

Step 1: Generate the NewTable with Account Statuses #

This query uses two left joins to compare each account's current week record with its previous and next week entries, then applies the status logic correctly:

SELECT
    b_current.Weekstart,
    b_current.ACCOUNTNo AS [ACCOUNT NO],
    CASE
        -- Handle the most recent week (Row_num=1) - no "next week" exists
        WHEN b_current.Row_num = 1 THEN
            CASE WHEN b_prev.ACCOUNTNo IS NOT NULL THEN 'Continue' ELSE 'New' END
        -- Handle all older weeks
        ELSE
            CASE
                WHEN b_prev.ACCOUNTNo IS NULL THEN 'New'          -- Not present in previous week
                WHEN b_next.ACCOUNTNo IS NULL THEN 'Expired'      -- Not present in next week
                ELSE 'Continue'                                   -- Present in both previous and next weeks
            END
    END AS Status
FROM BaseTable b_current
-- Join to check presence in the previous week (Row_num = current +1)
LEFT JOIN BaseTable b_prev 
    ON b_current.ACCOUNTNo = b_prev.ACCOUNTNo 
    AND b_prev.Row_num = b_current.Row_num + 1
-- Join to check presence in the next week (Row_num = current -1)
LEFT JOIN BaseTable b_next 
    ON b_current.ACCOUNTNo = b_next.ACCOUNTNo 
    AND b_next.Row_num = b_current.Row_num - 1
ORDER BY b_current.Weekstart DESC, Status;

This will exactly match your expected NewTable:

• For the most recent week (30/5/22), A1 is marked Continue (exists in the prior week) and B1 is New (doesn't exist in the prior week)
• For 23/5/22, A1 is Continue (exists in both prior and next weeks) and C1 is Expired (doesn't exist in the next week)
• For the oldest week (16/5/22), A1 is New (no prior week entry) and C1 is Continue (exists in the next week)

Step 2: Generate the Weekly Status Count Summary #

Wrap the above query in a subquery, then group by week and status to get your desired stats:

SELECT
    Weekstart,
    Status,
    COUNT(*) AS Count
FROM (
    SELECT
        b_current.Weekstart,
        CASE
            WHEN b_current.Row_num = 1 THEN
                CASE WHEN b_prev.ACCOUNTNo IS NOT NULL THEN 'Continue' ELSE 'New' END
            ELSE
                CASE
                    WHEN b_prev.ACCOUNTNo IS NULL THEN 'New'
                    WHEN b_next.ACCOUNTNo IS NULL THEN 'Expired'
                    ELSE 'Continue'
                END
        END AS Status
    FROM BaseTable b_current
    LEFT JOIN BaseTable b_prev 
        ON b_current.ACCOUNTNo = b_prev.ACCOUNTNo 
        AND b_prev.Row_num = b_current.Row_num + 1
    LEFT JOIN BaseTable b_next 
        ON b_current.ACCOUNTNo = b_next.ACCOUNTNo 
        AND b_next.Row_num = b_current.Row_num - 1
) AS StatusTable
GROUP BY Weekstart, Status
ORDER BY Weekstart DESC, Status;

This will return the exact weekly count breakdown you provided, showing how many accounts fall into each status category per week.

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