SQL Server拆分重叠日期范围：关联主副表获取有效时段数据

Got it, let's break down how to split overlapping date ranges for your scenario—this is a super common problem in database reporting, so I'll walk through it with concrete examples and SQL code you can adapt.

Scenario Recap #

You have a main Employment table with a person's core date range, plus sub tables (like Car and Food) that track values that change over time. The goal is to split the main date range into smaller intervals whenever any sub table value changes, and return each interval with the active sub table values (no per-date entries needed).

1. Sample Tables & Test Data #

First, let's define sample tables to mirror your setup:

Employment (Main Table) #

Stores the core period for a person:

CREATE TABLE Employment (
    PersonID INT,
    BeginDate DATE,
    EndDate DATE
);

INSERT INTO Employment VALUES
(1, '2023-01-01', '2023-12-31'); -- Employee 1's full-year employment

Car (Sub Table) #

Tracks changes to the employee's assigned car:

CREATE TABLE Car (
    PersonID INT,
    CarModel VARCHAR(50),
    EffectiveStart DATE,
    EffectiveEnd DATE
);

INSERT INTO Car VALUES
(1, 'Toyota Camry', '2023-01-01', '2023-04-30'),
(1, 'Honda Accord', '2023-05-01', '2023-09-30'),
(1, 'Tesla Model 3', '2023-10-01', '2023-12-31');

Food (Sub Table) #

Tracks changes to the employee's meal allowance:

CREATE TABLE Food (
    PersonID INT,
    MealAllowance DECIMAL(10,2),
    EffectiveStart DATE,
    EffectiveEnd DATE
);

INSERT INTO Food VALUES
(1, 20.00, '2023-01-01', '2023-06-30'),
(1, 25.00, '2023-07-01', '2023-12-31');

2. Step-by-Step Solution #

The core idea is to:

1. Collect all dates where any change happens (start/end of main period, plus all effective dates from sub tables)
2. Generate continuous intervals from these dates (within the main period)
3. Match each interval to the active values in the sub tables

Step 1: Gather Critical Change Dates #

First, we pull all dates that trigger a split—this includes the main period's start/end, plus every start/end date from the sub tables. We adjust end dates by +1 to handle inclusive date ranges cleanly:

WITH CriticalDates AS (
    -- Main employment dates
    SELECT BeginDate AS CriticalDate FROM Employment WHERE PersonID = 1
    UNION
    SELECT EndDate + 1 AS CriticalDate FROM Employment WHERE PersonID = 1 -- +1 to capture the last day of the period
    UNION
    -- Car change dates
    SELECT EffectiveStart AS CriticalDate FROM Car WHERE PersonID = 1
    UNION
    SELECT EffectiveEnd + 1 AS CriticalDate FROM Car WHERE PersonID = 1
    UNION
    -- Food change dates
    SELECT EffectiveStart AS CriticalDate FROM Food WHERE PersonID = 1
    UNION
    SELECT EffectiveEnd + 1 AS CriticalDate FROM Food WHERE PersonID = 1
),

Step 2: Create Split Date Ranges #

Next, we sort these critical dates and turn them into consecutive intervals, filtering out any dates outside the main employment period:

SplitRanges AS (
    SELECT
        CriticalDate AS PeriodStart,
        LEAD(CriticalDate) OVER (ORDER BY CriticalDate) - 1 AS PeriodEnd
    FROM CriticalDates
    WHERE CriticalDate <= (SELECT EndDate FROM Employment WHERE PersonID = 1)
)

Step 3: Join to Get Active Sub Table Values #

Finally, we join the split intervals back to the main table and sub tables to get the active values for each period. Use LEFT JOIN if you need to handle cases where a sub table might have no data for a person:

SELECT
    e.PersonID,
    sr.PeriodStart,
    sr.PeriodEnd,
    c.CarModel,
    f.MealAllowance
FROM Employment e
JOIN SplitRanges sr 
    ON sr.PeriodStart >= e.BeginDate 
    AND sr.PeriodEnd <= e.EndDate
LEFT JOIN Car c 
    ON e.PersonID = c.PersonID 
    AND sr.PeriodStart BETWEEN c.EffectiveStart AND c.EffectiveEnd
LEFT JOIN Food f 
    ON e.PersonID = f.PersonID 
    AND sr.PeriodStart BETWEEN f.EffectiveStart AND f.EffectiveEnd
WHERE e.PersonID = 1
ORDER BY sr.PeriodStart;

3. Expected Output #

The result will split the original full-year range into 4 intervals, each corresponding to a point where either the car model or meal allowance changed:

4. Key Tips for Adaptation #

• Add More Sub Tables: Just extend the CriticalDates CTE with UNION clauses for additional tables, and add another LEFT JOIN to include their values in the final select.
• Handle Multiple People: Remove the PersonID = 1 filters if you need to run this for all employees—just make sure all joins use PersonID to group correctly.
• Inclusive vs Exclusive Dates: If your sub tables use exclusive end dates (e.g., EffectiveEnd is the first day the value is no longer active), skip the +1 adjustment in the CriticalDates CTE.

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