SQL查询需求：为预测版本补全对应历史数据

Alright, let's work through this SQL challenge together. The core requirement here is to combine historical data with each forecast version correctly—where every forecast includes all historical data up to its starting month, while keeping the original historical dataset intact. We'll use the HistoryTo field from the Version table to align with the period in the Balance table (calculated as AYear*100 + APer).

Example Table Structures #

First, let's define the sample table structures to set the context:

-- Version table: Tracks forecast versions and their historical cutoff period
CREATE TABLE Version (
    VersionId INT PRIMARY KEY,
    HistoryTo INT -- Format: YYYYMM (e.g., 202403 = March 2024)
);

-- #Balance temporary table: Stores historical (VersionId=0) and forecast (VersionId>0) data
CREATE TABLE #Balance (
    VersionId INT,
    AYear INT,
    APer INT,
    Amount DECIMAL(18,2)
    -- Add other relevant fields here
);

Test Data #

Let's populate these tables with sample data to validate our solution:

-- Insert Version records
INSERT INTO Version (VersionId, HistoryTo)
VALUES 
    (0, NULL),       -- Original historical version
    (1, 202403),     -- Forecast version 1: starts in April 2024 (history cutoff March 2024)
    (2, 202406);     -- Forecast version 2: starts in July 2024 (history cutoff June 2024)

-- Insert Balance records
INSERT INTO #Balance (VersionId, AYear, APer, Amount)
VALUES 
    -- Historical data (VersionId=0)
    (0, 2024, 1, 100.00),
    (0, 2024, 2, 200.00),
    (0, 2024, 3, 300.00),
    (0, 2024, 4, 400.00),
    (0, 2024, 5, 500.00),
    (0, 2024, 6, 600.00),
    -- Forecast version 1 data
    (1, 2024, 4, 450.00),
    (1, 2024, 5, 550.00),
    -- Forecast version 2 data
    (2, 2024, 7, 750.00),
    (2, 2024, 8, 850.00);

Expected Output #

We want a result set that includes:

• The full original historical dataset (VersionId=0)
• For each forecast version, all historical data up to its HistoryTo period plus its own forecast data

Here's what that looks like:

Universal SQL Solution #

Here's a reusable script that meets all requirements:

-- 1. Keep the original historical data intact
SELECT 
    b.VersionId,
    b.AYear,
    b.APer,
    b.Amount
FROM #Balance b
WHERE b.VersionId = 0

UNION ALL

-- 2. For each forecast version, combine relevant historical data + its own forecast data
SELECT 
    v.VersionId,
    b.AYear,
    b.APer,
    b.Amount
FROM Version v
JOIN #Balance b ON 
    -- Include historical data up to the forecast's cutoff period
    (b.VersionId = 0 AND (b.AYear * 100 + b.APer) <= v.HistoryTo)
    -- Include the forecast version's own data
    OR (b.VersionId = v.VersionId)
WHERE v.VersionId > 0 -- Only process forecast versions
ORDER BY 
    v.VersionId,
    b.AYear,
    b.APer;

How This Works #

• First Segment: Directly pulls the full historical dataset (VersionId=0) to satisfy the requirement of retaining original history.
• Second Segment: Joins the Version table with #Balance to pair each forecast version with two sets of data:
  • Historical records where the calculated period (AYear*100+APer) is <= the forecast's HistoryTo cutoff.
  • The forecast version's own records.
• UNION ALL: Ensures we don't lose any records (no deduplication, since VersionId differentiates historical vs. forecast-included history).
• Sorting: Orders results by version and period for readability.

This script is universal because it dynamically uses the HistoryTo field—no hardcoded dates, so it will work as new forecast versions are added each month.

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