基于分区内最大日期生成结束日期——Transactions表处理需求

我有一张名为[pursuant].[dbo].[transactions]的数据库表，该表缺少结束日期字段，需基于表中的[Date]列生成结束日期，以在Tableau中进行日期差公式计算。每条记录对应一位Landowner、Status及状态开始日期Date，例如LandownerA在2024年1月1日状态为“Passed to Agent”，2024年1月22日状态变为“Outreach”。我希望将原表转换为包含结束日期的格式，但尝试的SQL代码报错，代码如下：

select 
    Landowner, Status, min(Date), max(Date)
from 
    (select 
         Transactions.*, 
         row_number() over (partition by Landowner, status order by Date) as seqnum
     from 
         Transactions t) t
group by 
    Landowner, Status, dateadd(day, - seqnum, Date)
order by 
    Landowner, min(Date);

要生成每个状态的起止日期，最直接的方法是用LEAD()窗口函数获取同一Landowner下下一条状态记录的日期，以此作为当前状态的结束日期。以下是修正后的SQL：

SELECT
    Landowner,
    Status,
    Date AS StartDate,
    -- 取下一条记录的日期减1天作为当前状态的结束日期，最后一条记录的结束日期设为NULL（可按需替换）
    DATEADD(DAY, -1, LEAD(Date) OVER (PARTITION BY Landowner ORDER BY Date)) AS EndDate
FROM
    [pursuant].[dbo].[transactions]
ORDER BY
    Landowner, StartDate;

关键说明 #

• LEAD(Date) OVER (PARTITION BY Landowner ORDER BY Date)：按Landowner分组、日期升序排序，获取当前行的下一行日期，这就是当前状态的“结束节点”。
• 用DATEADD(DAY, -1, ...)是为了让结束日期成为状态的最后一天（比如2024-01-01到2024-01-21是“Passed to Agent”状态，2024-01-22开始“Outreach”）。如果业务允许结束日期等于下一个状态的开始日期，可以去掉这个DATEADD。
• 最后一条记录没有后续状态，EndDate会返回NULL，你可以根据需求替换为GETDATE()（当前日期）或其他业务默认值。

原代码错误原因 #

1. 子查询中表引用歧义：外层表别名是t，但子查询里写Transactions.*而非t.*，可能导致数据库解析错误。
2. 分组逻辑错误：原代码按Landowner, Status, dateadd(day, - seqnum, Date)分组，这会把同一状态下的连续日期拆分成多个组，无法正确合并出状态的完整时间区间，同时分组字段未在SELECT列表中合理使用，违反SQL分组规则。

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