更新上一行的开始和结束日期

更新上一行的开始和结束日期

这适用于示例数据，但是如果可能存在多个合同，并且合同中的空合同无效 ，则 合同将失败。

declare @table table (id int, contract int, StartDate date, EndDate date)insert into @tablevalues(1000,1,'20170831',NULL),(1000,2,'20170916',NULL),(1000,3,'20170914',NULL);with cte as(select     id    ,contract    ,StartDate    ,EndDate    ,NewEndDate = case when StartDate > lead(StartDate) over (partition by id order by contract) then StartDate else  lead(StartDate) over (partition by id order by contract) endfrom @table t),cte2 as(select    id    ,contract    ,StartDate    ,EndDate    ,NewEndDate = case when NewEndDate = Lead(NewEndDate) over (partition by id order by contract) then Lead(StartDate,2) over (partition by id order by contract) else NewEndDate end from    cte)update cte2set EndDate = NewEndDateselect * from @table

在一行中编辑99个NULL并作废

declare @table table (id int, contract int, StartDate date, EndDate date)insert into @tablevalues(1000,1,'20170831',NULL),(1000,2,'20170916',NULL),(1000,2,'20170915',NULL),(1000,3,'20170914',NULL);with cte as(select     id    ,contract    ,StartDate    ,EndDate    ,NewEndDate =min(StartDate) over (partition by id order by contract ROWS BETWEEN 1 FOLLOWING AND 99 FOLLOWING )from        @table),cte2 as(select    id    ,contract    ,StartDate    ,EndDate    ,NewEndDate = isnull(case when NewEndDate = lag(NewEndDate) over (partition by id order by contract) then StartDate else NewEndDate end,'99991231')from    cte)update cte2set EndDate = NewEndDateselect * from @table