如何在SQL Server中重复缺少日期的最后一行？

可以使用表变量和 while 循环实现查询。使用 MIN 和 MAX 获取您想要的计算日期范围，while 循环可以简单地检查相关日期是否存在数据，如果没有，则使用 DATEADD（） 函数获取前几天的数据。我在下面列出了如何实现结果，但不得不对表格的结构做出一些假设。

--  Create a table variable to store the data...
DECLARE @CheckBalance AS TABLE
(
    BDate DATE NOT NULL,
    CRM VARCHAR(3) NOT NULL,
    ACCT VARCHAR(5) NULL,
    BAL INT
);

DECLARE @StartDate DATE, @EndDate DATE, @WorkingDate DATE;
--  Get the starting date and the end date you want to calculate...
--  Add an extra day to the end date for the while loop check.
SELECT @StartDate = MIN(BDate), @EndDate = DATEADD(DAY, 1, MAX(BDate)) FROM RunningBalance;

SELECT @WorkingDate = @StartDate;

WHILE @WorkingDate < @EndDate
BEGIN

    IF NOT EXISTS(SELECT 1 FROM RunningBalance WHERE BDate = @WorkingDate)
    BEGIN
        --  Since the data is missing from the WorkingDate, insert the previous days data.
        INSERT INTO @CheckBalance (BDate, CRM, ACCT, BAL)
            SELECT @WorkingDate AS BDate, CRM, ACCT, BAL FROM RunningBalance WHERE BDate = DATEADD(DAY, -1, @WorkingDate);
    END
    ELSE
    BEGIN
        --  Data exists, insert that.
        INSERT INTO @CheckBalance (BDate, CRM, ACCT, BAL)
            SELECT BDate, CRM, ACCT, BAL FROM RunningBalance WHERE BDate = @WorkingDate;
    END

    SELECT @WorkingDate = DATEADD(DAY, 1, @WorkingDate);

END

SELECT BDate, CRM, ACCT, BAL FROM @CheckBalance;

假设您有一个日期维度表，您可以联接该表以获得一个包含间隙和孤岛的表。然后，将有多个选项来填补空白（NULL 值）。这是一个经典的：

首先，让我们建立你的示例：

CREATE TABLE RUNNUNG_BALANCE_TABLE (
  BDATE DATE NOT NULL,
  CRM INT,
  ACCT NVARCHAR(5),
  BAL INT
);

INSERT INTO RUNNUNG_BALANCE_TABLE
VALUES
  ('2023-10-24',123,'ab123',1000),
  ('2023-10-25',123,'ab123',1100),
  ('2023-10-27',123,'ab123',1200),
  ('2023-10-28',123,'ab123',1300);

CREATE TABLE DIM_DATE (
  DATEKEY INT NOT NULL,
  DATE_ISO DATE NOT NULL
);

INSERT INTO DIM_DATE
VALUES
  (20231024,'2023-10-24'),
  (20231025,'2023-10-25'),
  (20231026,'2023-10-26'),
  (20231027,'2023-10-27'),
  (20231028,'2023-10-28'),
  (20231029,'2023-10-29'),
  (20231030,'2023-10-30');

然后，我们使用公用表表达式 （CTE） 将财务数据表与包含日期值的表联接起来，并创建一个新列来标记条目组。我们通过使用带有 COUNT 的 OVER 子句作为聚合函数来做到这一点。我们使用此列来确定“最后可用”的数据。我们可以在这里使用 MIN（），因为任何值总是大于 NULL。

WITH CTE AS (
  SELECT 
    DATE_ISO,
    CRM,
    ACCT,
    BAL,
    COUNT(CASE WHEN BAL IS NOT NULL THEN 1 END) OVER (ORDER BY DATE_ISO) AS grp
  FROM RUNNUNG_BALANCE_TABLE
  RIGHT JOIN DIM_DATE ON DATE_ISO = BDATE
)
SELECT
  DATE_ISO AS BDATE,
  MIN(CRM) OVER (PARTITION BY grp) AS CRM,
  MIN(ACCT) OVER (PARTITION BY grp) AS ACCT,
  MIN(BAL) OVER (PARTITION BY grp) AS BAL
FROM CTE;

CTE 如下所示：

最终结果如下所示：

SQL 小提琴