基于日期筛选对比合同列数据并标记新增/终止合同的实现方法咨询

Hey there! Let's work through how to spot new and ended contracts across different dates in your ECCR28 table. The key is to compare the list of contract numbers (CC column) between your target dates and flag the differences. Here are a few practical approaches depending on your needs:

1. Compare Two Specific Dates (e.g., 2022-12-07 vs 2022-12-08) #

If you want to check changes between two fixed dates, you can use a full outer join to match contracts from both dates and identify gaps:

WITH prev_date_contracts AS (
    -- Get all unique contracts from the previous date
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = '2022-12-07' -- Replace with your prior date
),
current_date_contracts AS (
    -- Get all unique contracts from the current date
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = '2022-12-08' -- Replace with your current date
)
SELECT
    COALESCE(p.CC, c.CC) AS CC,
    CASE
        WHEN p.CC IS NULL THEN 'NEW CONTRACT' -- Only exists in current date
        WHEN c.CC IS NULL THEN 'CONTRACTION ENDED' -- Only exists in previous date
        ELSE 'NO CHANGE' -- Exists in both dates
    END AS CONTRACT_STATUS
FROM prev_date_contracts p
FULL OUTER JOIN current_date_contracts c ON p.CC = c.CC;

How this works:

• We use two CTEs (prev_date_contracts and current_date_contracts) to get distinct contracts from each date (to avoid duplicates if a contract appears multiple times on the same date).
• The full outer join combines all contracts from both dates. The CASE statement checks where each contract exists to assign its status.

2. Compare the Latest Date with the Immediate Prior Date (Dynamic) #

If you want to automatically compare the most recent date in your table with the one before it, use window functions to rank dates first:

WITH date_ranks AS (
    -- Rank all distinct dates in descending order (latest first)
    SELECT DISTINCT REFERENCIA,
           RANK() OVER (ORDER BY REFERENCIA DESC) AS date_rank
    FROM ECCR28
),
latest_dates AS (
    -- Grab the top two dates (latest and previous)
    SELECT REFERENCIA
    FROM date_ranks
    WHERE date_rank <= 2
),
prev_date_contracts AS (
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = (SELECT REFERENCIA FROM latest_dates WHERE date_rank = 2)
),
current_date_contracts AS (
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = (SELECT REFERENCIA FROM latest_dates WHERE date_rank = 1)
)
SELECT
    COALESCE(p.CC, c.CC) AS CC,
    CASE
        WHEN p.CC IS NULL THEN 'NEW CONTRACT'
        WHEN c.CC IS NULL THEN 'CONTRACTION ENDED'
        ELSE 'NO CHANGE'
    END AS CONTRACT_STATUS
FROM prev_date_contracts p
FULL OUTER JOIN current_date_contracts c ON p.CC = c.CC;

3. For MySQL (No Full Outer Join Support) #

If you're using MySQL (which doesn't support full outer joins), you can use UNION ALL to combine three separate queries:

WITH prev_date_contracts AS (
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = '2022-12-07'
),
current_date_contracts AS (
    SELECT DISTINCT CC
    FROM ECCR28
    WHERE REFERENCIA = '2022-12-08'
)
-- New contracts (only in current date)
SELECT CC, 'NEW CONTRACT' AS CONTRACT_STATUS
FROM current_date_contracts
WHERE CC NOT IN (SELECT CC FROM prev_date_contracts)

UNION ALL

-- Ended contracts (only in previous date)
SELECT CC, 'CONTRACTION ENDED' AS CONTRACT_STATUS
FROM prev_date_contracts
WHERE CC NOT IN (SELECT CC FROM current_date_contracts)

UNION ALL

-- Unchanged contracts (exists in both dates)
SELECT CC, 'NO CHANGE' AS CONTRACT_STATUS
FROM prev_date_contracts
WHERE CC IN (SELECT CC FROM current_date_contracts);

Quick Tips: #

• Ensure your REFERENCIA column is stored as a date type (not string) to avoid issues with date comparisons.
• Adjust the date values in the queries to match your actual data.
• If you need to track changes across multiple dates over time, you can extend this approach using LAG() window functions to compare each date with the one before it sequentially.

备注：内容来源于stack exchange，提问作者Deforceh