如何基于序列位置编号创建新的开始日期和结束日期:
从
EmpNo StartDate EndDate PositionNumber
832319 4/05/2020 10/05/2020 32681
832319 11/05/2020 17/05/2020 32681
832319 18/05/2020 24/05/2020 32681
832319 25/05/2020 31/05/2020 32681
832319 1/06/2020 7/06/2020 32681
832319 8/06/2020 14/06/2020 32681
832319 15/06/2020 21/06/2020 32783
832319 22/06/2020 28/06/2020 32783
832319 29/06/2020 5/07/2020 32783
832319 6/07/2020 12/07/2020 32781
832319 13/07/2020 19/07/2020 32781
832319 20/07/2020 26/07/2020 32681
832319 27/07/2020 2/08/2020 32681
要(为新的开始日期和新的结束日期创建两个新字段)
EmpNo StartDate EndDate PositionNumber NewStartDate New EndDate
832319 4/05/2020 10/05/2020 32681 4/05/2020 14/06/2020
832319 11/05/2020 17/05/2020 32681 4/05/2020 14/06/2020
832319 18/05/2020 24/05/2020 32681 4/05/2020 14/06/2020
832319 25/05/2020 31/05/2020 32681 4/05/2020 14/06/2020
832319 1/06/2020 7/06/2020 32681 4/05/2020 14/06/2020
832319 8/06/2020 14/06/2020 32681 4/05/2020 14/06/2020
832319 15/06/2020 21/06/2020 32783 21/06/2020 5/07/2020
832319 22/06/2020 28/06/2020 32783 21/06/2020 5/07/2020
832319 29/06/2020 5/07/2020 32783 21/06/2020 5/07/2020
832319 6/07/2020 12/07/2020 32781 6/07/2020 19/07/2020
832319 13/07/2020 19/07/2020 32781 6/07/2020 19/07/2020
832319 20/07/2020 26/07/2020 32681 20/07/2020 2/08/2020
832319 27/07/2020 2/08/2020 32681 20/07/2020 2/08/2020
任何帮助表示赞赏。
I understand this as a gaps-and-island problem. You want to the minimum and maximum date of "adjacent" rows sharing the same
empNo
andpositionNo
.Here is an approach using window functions. The idea is to use
lag()
and a cumulativesum()
to define the groups:您是否正在寻找最小开始日期和最大结束日期?