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