求生成连续TranMonth并复制上月数据的SQL查询语句

现有查询语句如下：

SELECT 
    i.ItemNbr,
    i.TranMonth,
    i.Warehouse,
    i.OpeningQty,
    i.OpeningPrice,
    i.OpeningAmt
FROM  
    Inventory i
WHERE 
    i.ItemNbr IN ('00188613')

该查询返回的结果中TranMonth存在不连续的情况，财务团队要求：

• TranMonth必须连续，缺失的月份需复制上月对应数据填充
• 数据需展示至当前月

测试用表结构及数据：

CREATE TABLE Inventory
(
    ItemNbr NVARCHAR(20),
    TranMonth CHAR(7),
    Warehouse CHAR(2),
    OpeningQty NUMERIC(18, 4),
    OpeningPrice NUMERIC(18, 4),
    OpeningAmt NUMERIC(18, 4)
)

INSERT INTO Inventory
SELECT '00188613','2024-01','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-04','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-05','01',5.0000,303.100000,1515.5000
UNION
SELECT '00188613','2024-06','01',4.0000,303.100000,1212.4000
UNION
SELECT '00188613','2024-12','01',4.0000,365.400000,1461.6000

期望结果示例（补全连续月份并填充缺失数据）：

ItemNbr    TranMonth    Warehouse   OpeningQty  OpeningPrice    OpeningAmt
--------------------------------------------------------------------------
00188613    2024-01     01          4.0000      303.1           1212.4
00188613    2024-02     01          4.0000      303.1           1212.4
00188613    2024-03     01          4.0000      303.1           1212.4
00188613    2024-04     01          4.0000      303.1           1212.4
00188613    2024-05     01          5.0000      303.1           1515.5
00188613    2024-06     01          4.0000      303.1           1212.4
00188613    2024-07     01          4.0000      303.1           1212.4
00188613    2024-08     01          4.0000      303.1           1212.4
00188613    2024-09     01          4.0000      303.1           1212.4
00188613    2024-10     01          4.0000      303.1           1212.4
00188613    2024-11     01          4.0000      303.1           1212.4
00188613    2024-12     01          4.0000      365.4           1461.6
00188613    2025-01     01          4.0000      365.4           1461.6

WITH DateRange AS (
    -- 生成从最早月份到当前月的连续月份序列
    SELECT MIN(CAST(TranMonth + '-01' AS DATE)) AS StartDate,
           DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS EndDate
    FROM Inventory
    WHERE ItemNbr = '00188613'

    UNION ALL

    SELECT DATEADD(MONTH, 1, StartDate), EndDate
    FROM DateRange
    WHERE StartDate < EndDate
),
MonthlyData AS (
    -- 将原数据转换为日期格式，方便关联
    SELECT 
        ItemNbr,
        CAST(TranMonth + '-01' AS DATE) AS MonthDate,
        Warehouse,
        OpeningQty,
        OpeningPrice,
        OpeningAmt
    FROM Inventory
    WHERE ItemNbr = '00188613'
),
FilledData AS (
    -- 关联连续月份和原数据，使用LAST_VALUE填充缺失值
    SELECT 
        d.ItemNbr,
        FORMAT(d.MonthDate, 'yyyy-MM') AS TranMonth,
        LAST_VALUE(m.Warehouse) OVER (PARTITION BY d.ItemNbr ORDER BY d.MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Warehouse,
        LAST_VALUE(m.OpeningQty) OVER (PARTITION BY d.ItemNbr ORDER BY d.MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OpeningQty,
        LAST_VALUE(m.OpeningPrice) OVER (PARTITION BY d.ItemNbr ORDER BY d.MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OpeningPrice,
        LAST_VALUE(m.OpeningAmt) OVER (PARTITION BY d.ItemNbr ORDER BY d.MonthDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OpeningAmt
    FROM (
        SELECT DISTINCT '00188613' AS ItemNbr, StartDate AS MonthDate
        FROM DateRange
    ) d
    LEFT JOIN MonthlyData m ON d.MonthDate = m.MonthDate
)
SELECT * FROM FilledData ORDER BY TranMonth;

1. 生成连续月份序列：使用递归CTE DateRange，从原数据中最早的TranMonth开始，生成到当前月的所有连续月份。
2. 转换日期格式：将原数据的TranMonth（yyyy-MM格式）转换为日期类型（yyyy-MM-01），方便后续关联。
3. 填充缺失数据：通过LEFT JOIN关联连续月份和原数据，使用LAST_VALUE窗口函数，按月份顺序取最近的非空值填充缺失的月份数据，确保每个月份都有对应值。

内容的提问来源于stack exchange，提问作者Peter Nguy Nguyen