更新前一行的开始和结束日期 [英] updating start and end dates of previous rows

问题描述

这个例子比仅仅根据新记录的开始日期更新前一条记录要复杂一些.我希望你能帮忙.

This example is a bit trickier than just updating the previous record based on the start date of a new record. I was hoping you could help.

ID 1000(有很多 ID，我们需要分区?)有一个初始开始日期.

ID 1000 (there are many ID's and we would need to partition?) has an initial start date.

该 ID 与另一份合同相关联.因此，第一个合同的结束日期是第二个合同的开始日期.请注意，第二份合同的日期可能会也可能不会.

The ID gets linked to another contract. Hence, the end date of the 1st contract is the start date of the second contract. Note that the second contract may or may not be future dated.

但是，在第 2 个合约开始之前，ID 可能会链接到不同的合约.所以第二份合同无效.现在第 3 个合同优先，第一个合同的结束日期需要更改为第 3 个合同的开始日期.第二个合同保持显示开始和结束日期相同.

However, the ID may get linked to a different contract before the 2nd contract even starts. So the second contract becomes null and void. The 3rd contract now takes precedence and the end date of the first contract needs to be changed to the start date of the 3rd contract. The second contract is kept showing the start and end date being the same.

关于如何使用 T-SQL 实现这一目标的任何想法?

Any ideas on how to achieve this using T-SQL?

id       contract    Start Date   End Date 
1000        1       2017/08/31   9999/12/31


id       contract    Start Date   End Date 
1000        1       2017/08/31   2017/09/16 
1000        2       2017/09/16   9999/12/31

id       contract    Start Date   End Date  
1000        1       2017/08/31   2017/09/14 
1000        2       2017/09/16   2017/09/16 
1000        3       2017/09/14   9999/12/31

提前致谢.

亲切的问候

推荐答案

这适用于示例数据，但如果连续存在 1 个以上无效的合约，则会失败>.

This works for the sample data, but would fail if there could be more than 1 contract that would be null and void in a row.

declare @table table (id int, contract int, StartDate date, EndDate date)
insert into @table
values
(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) end
from @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 cte2
set EndDate = NewEndDate

select * from @table

连续编辑 99 个 NULL 和 VOID

declare @table table (id int, contract int, StartDate date, EndDate date)
insert into @table
values
(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 cte2
set EndDate = NewEndDate

select * from @table

这篇关于更新前一行的开始和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！