生效失效的问题

Question

编码               生效日期                  失效日期
901220660001       2006-10-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2007-04-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2007-06-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2007-10-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2007-12-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2008-06-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2008-08-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2009-01-01 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2009-06-10 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2009-08-06 00:00:00.000 2010-07-20 00:00:00.000
901220660001       2009-12-01 00:00:00.000 2010-07-20 00:00:00.000我要把失效日期更新为下一个生效日期-1
比如说第一条的失效。。应该更新为20070330

Answer 1

--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go
create table tb (编码 bigint,生效日期 datetime,失效日期 datetime)
insert into tb
select 901220660001,'2006-10-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-04-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-06-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-10-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-12-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2008-06-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2008-08-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-01-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-06-10 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-08-06 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-12-01 00:00:00.000','2010-07-20 00:00:00.000'
update a set 失效日期=dateadd(day,-1,((select top 1 生效日期 from tb where a.编码=编码 and a.生效日期<生效日期 order by 生效日期 asc)))
from tb aselect * from tb
  编码                   生效日期                    失效日期
-------------------- ----------------------- -----------------------
901220660001         2006-10-01 00:00:00.000 2007-03-31 00:00:00.000
901220660001         2007-04-01 00:00:00.000 2007-05-31 00:00:00.000
901220660001         2007-06-01 00:00:00.000 2007-09-30 00:00:00.000
901220660001         2007-10-01 00:00:00.000 2007-11-30 00:00:00.000
901220660001         2007-12-01 00:00:00.000 2008-05-31 00:00:00.000
901220660001         2008-06-01 00:00:00.000 2008-07-31 00:00:00.000
901220660001         2008-08-01 00:00:00.000 2008-12-31 00:00:00.000
901220660001         2009-01-01 00:00:00.000 2009-06-09 00:00:00.000
901220660001         2009-06-10 00:00:00.000 2009-08-05 00:00:00.000
901220660001         2009-08-06 00:00:00.000 2009-11-30 00:00:00.000
901220660001         2009-12-01 00:00:00.000 NULL(11 行受影响)

Answer 2

;with cte as
(
select * ,rn=ROW_NUMBER()over(PARTITION BY 编码 order by getdate()) from tb 
)
update a
set 失效日期=isnull(b.生效日期-1,GETDATE())
from cte a left join cte b on a.编码=b.编码 and a.rn=b.rn-1 

Answer 3

if object_id('tempdb..#your_table','U') is not null
    drop table #your_table
go
create table #your_table(
    编码      bigint,
    生效日期  datetime,
    失效日期  datetime
)
insert into #your_table
select 901220660001,'2006-10-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-04-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-06-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-10-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2007-12-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2008-06-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2008-08-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-01-01 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-06-10 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-08-06 00:00:00.000','2010-07-20 00:00:00.000' union all
select 901220660001,'2009-12-01 00:00:00.000','2010-07-20 00:00:00.000' --把#your_table中的数据按照生效日期从大到小倒序插入临时表#中,并增加自增列ID
drop table #
select ID=identity(int,1,1),*
into #
from #your_table
order by 生效日期 desc
select * from #
--更新临时表中的失效日期
declare @start_date1 datetime
declare @start_date2 datetime--倒序update
update #
set 失效日期 = case when ID = 1 then 失效日期 else dateadd(day,-1,@start_date2) end
    ,@start_date2 = @start_date1
    ,@start_date1=生效日期
    
--查询更新结果
select * from #
/*
ID          编码                   生效日期                    失效日期
----------- -------------------- ----------------------- -----------------------
1           901220660001         2009-12-01 00:00:00.000 2010-07-20 00:00:00.000
2           901220660001         2009-08-06 00:00:00.000 2009-11-30 00:00:00.000
3           901220660001         2009-06-10 00:00:00.000 2009-08-05 00:00:00.000
4           901220660001         2009-01-01 00:00:00.000 2009-06-09 00:00:00.000
5           901220660001         2008-08-01 00:00:00.000 2008-12-31 00:00:00.000
6           901220660001         2008-06-01 00:00:00.000 2008-07-31 00:00:00.000
7           901220660001         2007-12-01 00:00:00.000 2008-05-31 00:00:00.000
8           901220660001         2007-10-01 00:00:00.000 2007-11-30 00:00:00.000
9           901220660001         2007-06-01 00:00:00.000 2007-09-30 00:00:00.000
10          901220660001         2007-04-01 00:00:00.000 2007-05-31 00:00:00.000
11          901220660001         2006-10-01 00:00:00.000 2007-03-31 00:00:00.000(11 行受影响)
*/