如何查询2023.1.1-1.31日期范围内客户的每日状态变化？

需要追踪指定日期范围（2023年1月1日至2023年1月31日）内客户的状态变化，现有两张表：

表1：Customer #

表2：StatusHistory #

期望得到该日期范围内每日对应的客户状态，示例如下：

尝试了如下SQL查询，但未得到预期结果，请问如何实现该需求？

WITH StatusChanges AS (
  SELECT filekey, status, effdate,
         ROW_NUMBER() OVER (PARTITION BY filekey ORDER BY effdate) AS rn
  FROM empactstatushist
  WHERE filekey = 1000 AND effdate <= '2023-01-31'
),
Dates AS (
  SELECT DATEADD(day, n, '2023-01-01') AS DateValue
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) - 1 AS n
    FROM sys.objects a
  ) AS n
  WHERE DATEADD(day, n, '2023-01-01') < '2023-02-01'
)
SELECT
  d.DateValue,
  filekey,
  sc.status
FROM Dates d
LEFT JOIN StatusChanges sc ON sc.filekey = 1000
  AND sc.effdate = (
    SELECT MAX(effdate)
    FROM StatusChanges sc2
    WHERE sc2.filekey = 1000 AND sc2.effdate <= d.DateValue
  );

原SQL仅使用了StatusHistory表，未整合Customer表中1/15/2023的状态变更记录，这是结果不符合预期的核心原因。需要先合并两张表的状态变更数据，再与日期表关联匹配每日状态。

修改后的SQL如下：

WITH AllStatusChanges AS (
    -- 合并两张表的状态变更记录
    SELECT filekey, status, effdate
    FROM StatusHistory
    WHERE filekey = 1000 AND effdate <= '2023-01-31'
    UNION ALL
    SELECT filekey, activestatus AS status, activestatuseffdate AS effdate
    FROM Customer
    WHERE filekey = 1000 AND activestatuseffdate <= '2023-01-31'
),
Dates AS (
    -- 生成2023年1月1日至31日的所有日期
    SELECT DATEADD(day, n, '2023-01-01') AS DateValue
    FROM (
        SELECT TOP 31 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.objects
    ) AS num
    WHERE DATEADD(day, n, '2023-01-01') <= '2023-01-31'
)
SELECT
    d.DateValue,
    1000 AS filekey,
    -- 获取当前日期前最新生效的状态
    (SELECT TOP 1 status
     FROM AllStatusChanges sc
     WHERE sc.filekey = 1000 AND sc.effdate <= d.DateValue
     ORDER BY sc.effdate DESC) AS status
FROM Dates d
ORDER BY d.DateValue;

关键修改说明： #

• 合并状态数据：新增AllStatusChanges公共表表达式，将两张表的状态变更记录合并，确保包含1/15/2023的状态更新。
• 优化日期生成：用TOP 31直接生成1月的31天，简化日期生成逻辑。
• 匹配每日状态：通过子查询按日期倒序取最新状态，保证每个日期都能拿到对应生效的状态值。

执行该SQL后即可得到预期的每日客户状态结果。

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