编写SQL检查M-Plan的连续时间线分配情况

Got it, let's break down how to check if the active Grants assigned to each M-Plan form a continuous, unbroken timeline. The goal is to verify each Grant starts exactly the day after the previous one ends—flagging any gaps or overlaps along the way. Here's a practical SQL solution tailored to your schema:

Step-by-Step SQL Solution #

First, we'll use common table expressions (CTEs) to break the problem into manageable parts, then add logic to validate timeline continuity.

WITH AssignedActiveGrants AS (
    -- 1. Join tables and filter for active timeline Grants
    SELECT 
        mp.M_Plan_ID,
        g.GID,
        -- Convert string dates to proper date type (adjust for your database):
        -- Oracle: TO_DATE(g.START_DATE, 'DD/MM/YYYY')
        -- SQL Server: CONVERT(DATE, g.START_DATE, 103)
        -- PostgreSQL: TO_DATE(g.START_DATE, 'DD/MM/YYYY')
        TO_DATE(g.START_DATE, 'DD/MM/YYYY') AS START_DATE,
        -- Normalize end dates: treat NULL as the far-future sentinel value
        CASE 
            WHEN g.END_DATE IS NULL THEN TO_DATE('31/12/9999', 'DD/MM/YYYY')
            ELSE TO_DATE(g.END_DATE, 'DD/MM/YYYY')
        END AS END_DATE
    FROM 
        M_Plan mp
    JOIN 
        M_Plan_Grant_Association assoc ON mp.M_Plan_ID = assoc.M_Plan_ID
    JOIN 
        Grant g ON assoc.GID = g.GID
    WHERE 
        g.ACTIVE_TIME_LINE = 'Y' -- Ignore inactive timeline entries
),
OrderedGrants AS (
    -- 2. Order Grants for each M-Plan and get the previous Grant's end date
    SELECT 
        *,
        LAG(END_DATE) OVER (PARTITION BY M_Plan_ID ORDER BY START_DATE) AS Previous_Grant_End_Date
    FROM 
        AssignedActiveGrants
)
-- 3. Check for continuity gaps, overlaps, or valid sequences
SELECT 
    M_Plan_ID,
    GID,
    START_DATE,
    END_DATE,
    Previous_Grant_End_Date,
    CASE 
        WHEN Previous_Grant_End_Date IS NULL THEN 'First Grant in sequence'
        WHEN START_DATE = Previous_Grant_End_Date + INTERVAL '1' DAY THEN 'Continuous with previous Grant'
        WHEN START_DATE <= Previous_Grant_End_Date THEN 'Overlaps with previous Grant'
        ELSE 'Gap exists between this and previous Grant'
    END AS Timeline_Status
FROM 
    OrderedGrants
-- Optional: Uncomment to show only problematic records
-- WHERE START_DATE <> Previous_Grant_End_Date + INTERVAL '1' DAY AND Previous_Grant_End_Date IS NOT NULL
ORDER BY 
    M_Plan_ID, 
    START_DATE;

Key Details Explained #

• AssignedActiveGrants CTE: This pulls together all active Grants linked to each M-Plan. We convert string dates to proper date types (adjust the conversion function to match your database) and normalize NULL end dates to 9999-12-31 to simplify continuity checks for open-ended Grants.
• OrderedGrants CTE: The LAG() window function grabs the end date of the immediately preceding Grant for each M-Plan (sorted by start date). This lets us directly compare each Grant to the one before it.
• Final Query: The Timeline_Status column clearly labels each Grant's position in the timeline—whether it's the first entry, continuous with the prior Grant, overlapping, or creating a gap.

Quick Variant: Get Overall M-Plan Timeline Status #

If you just want a high-level view of which M-Plans have broken timelines, use this aggregated version:

WITH AssignedActiveGrants AS (
    -- Same as above
),
OrderedGrants AS (
    -- Same as above
),
TimelineIssues AS (
    SELECT 
        M_Plan_ID,
        COUNT(CASE WHEN START_DATE <> Previous_Grant_End_Date + INTERVAL '1' DAY AND Previous_Grant_End_Date IS NOT NULL THEN 1 END) AS Problem_Count
    FROM 
        OrderedGrants
    GROUP BY 
        M_Plan_ID
)
SELECT 
    M_Plan_ID,
    CASE 
        WHEN Problem_Count = 0 THEN 'Timeline is fully continuous'
        ELSE 'Timeline has gaps or overlaps'
    END AS Overall_Timeline_Status
FROM 
    TimelineIssues;

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