将一个组的连续日期合并在一起

Question

我有一个看起来像这样的员工的桌子：

名称	部门	经理	日期
员工1	部门1	经理x	202101
员工1	dept 1	Manager x	202102
雇员1雇员1	dept 2	Manager x	202103
雇员1	雇员1部长2	经理X	202104
雇员1部门1	Manager 1	Manager x	202105
员工1	部门1	经理X	202106
员工2	部门1	经理x	202101
员工2	雇员2 dept 1	Manager x	202102

我需要构建以下格式显示数据的视图：

名称	部门	经理	有效_for_from	valos valy_to
雇员1	dept 1	Manager x	202101	202102
雇员1	部门2	Manager x	202103	202104
员工1	部门1	Manager x	202105	999912
员工2	dept 1	Manager x	202101	999912

到目前为止，这就是为什么代码的样子：

WITH cte AS
(
   SELECT [Name], Department, Manager, Valid_From = min([Date]), Valid_To = max([Date]),
      RowNum = ROW_NUMBER() OVER (PARTITION BY [Name], ORDER BY max([Date]) DESC)
   FROM TestingTable
   WHERE ([Date] IS NOT NULL)
   GROUP BY [Name], Department, Manager
)
SELECT [Name], Department, Manager, Valid_From,
    CASE WHEN RowNum = 1 THEN 999912 ELSE Valid_To END AS Valid_To, CASE WHEN RowNum = 1 THEN 1 ELSE 0 END AS Is_Latest
FROM cte

输出就是这样 - IT组在雇员1在部门的间隔工作，该雇员1在部门工作了1，虽然我需要以2个不同的时间间隔。

名称	部门	经理	有效	_for_to
雇员1	部门1	经理X	202101	999912
员工1	部门2	Manager x	202103	202104
员工2	dept 1	Manager x	202101	999912

我尝试了一些lag和Lag功能以比较日期，但我迷失了。

Answer 1

看来您需要连续将每个员工 - 居民经理人分组在一起。可以这样做：

with cte1 as (
    select name
         , department
         , manager
         , datefromparts(date / 100, date % 100, 1) as yymm
    from t
), cte2 as (
    select *
         , case when lag(yymm) over (partition by name, department, manager order by yymm) = dateadd(month, -1, yymm) then 0 else 1 end as new_grp
    from cte1
), cte3 as (
    select *
         , sum(new_grp) over (partition by name, department, manager order by yymm) as grp_num
    from cte2
)
select name
     , department
     , manager
     , min(yymm) as valid_from
     , max(yymm) as valid_to
from cte3
group by name, department, manager, grp_num
order by name, valid_from, department, manager

请注意，我必须将年度月份转换为日期，以便更容易地进行比较。结果：

姓名	部门	经理	有效_from	valy_to
雇员1	部长1	经理x	2021-01-01	2021-02-01
雇员	1部长1	部长x	2021-03-03-01	2021-04-04-01
雇员	1 dept 1	Manager 1 Manager x	2021-05-01-05-01	2021-06-01
员工2	部门1	Manager x	2021-01-01	2021-02-02-02-01

用9999-12-01是trivial例如，您可以检查Lead（有效_from）是否（按vall valom按名称顺序划分）为null。

Answer 2

Looks like a kind of gaps and islands problem with a twist regarding the open intervals detection

select distinct Name, Department, Manager, 
      min([date]) over(partition by Name, g) fromd,
      max([date]) over(partition by Name, g) tod
from (
  select *, sum(flag) over(partition by Name order by [date]) g
  from (
    select Name, Department, Manager,
      case when lead(name) over(partition by Name order by [date]) is null then 999912 else [date] end [date],
      case when department != lag(Department, 1, '') over(partition by Name order by [date]) 
            or  Manager != lag(Manager, 1, '') over(partition by Name order by [date])
           then 1 else 0 end flag
    from tbl
  ) t
) t
order by fromd

db<>小提琴

Answer 3

尝试以下操作：

with tbl1 as (select 
    [name]
    ,[department]
    ,[manager]
    ,[date]
    ,ROW_NUMBER () over (partition by name, department, manager order by name, department) as rownum 
from employees)
select  [name]
    ,[department]
    ,[manager]
    ,[date] as [valid_from]
    ,(select [date] from tbl1 t2 
    where t2.[rownum]  = t1.[rownum] + 1
    and t1.name = t2.name
    and t1.department = t2.department
    and t1.manager = t2.manager
    ) as [valid_to]
from tbl1 t1
where rownum % 2 = 1
order by name, valid_from

它缺少999912，因为我不了解在底部两行中更换的逻辑。