根据序列位置编号创建新的开始日期和结束日期，

由 Gerber发布于 2020-05-18 07:53:20

如何基于序列位置编号创建新的开始日期和结束日期：

从

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

任何帮助表示赞赏。

不No

2020-05-18 07:53:21

I understand this as a gaps-and-island problem. You want to the minimum and maximum date of "adjacent" rows sharing the same empNo and positionNo.

Here is an approach using window functions. The idea is to use lag() and a cumulative sum() to define the groups:

select 
    empNo,
    startDate,
    endDate,
    positionNumber,
    min(startDate) over(partition by empNo, positionNumber, grp) newStartDate,
    max(endDate)   over(partition by empNo, positionNumber, grp) newEndDate
from (
    select
        select 
            t.*,
            sum(case when startDate = dateadd(day, 1, lagEndDate) then 0 else 1 end)
                over(partition by empNo, positionNumber order by endDate) grp
    from (
        select 
            t.*, 
            lag(endDate) 
                over(partition by empNo, positionNumber order by endDate) lagEndDate
        from mytable t
    ) t
) t
order by empNo, startDate

pporro

2020-05-18 07:53:21

您是否正在寻找最小开始日期和最大结束日期？

select t.*,
       min(startdate) over (partition by empno, positionnumber) as new_startdate,
       min(enddate) over (partition by empno, positionnumber) as new_enddate
from t

Gerber

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3233
注册排名
1338