sql - 显示水平列出的状态之间的天数

Question

我有下表，它代表票务数据库的操作细节。我想要做的是水平显示每张票的最后 10 个不同的操作，以及每个状态与前一个状态之间的日期差异(以天为单位)。

这是原始表:

Case_number Operation_Name  Date
112345      Canceled        22.04.2018
112345      On hold         20.04.2018
112345      On hold         15.04.2018
112345      Processing      10.04.2018
112345      Open            08.04.2018
112347      Closed          21.04.2018
112347      On hold         20.04.2018
112347      On hold         18.04.2018
112347      Processing      15.04.2018
112347      Processing      13.04.2018
112347      Open            11.04.2018

输出应该是这样的

如你所见:

112347      On hold         20.04.2018
112347      On hold         18.04.2018

可能存在重复状态，我必须为其提取第一个日期 (18.04.2018)。

我尝试使用 lead 函数，但没有成功。

提前感谢您抽出宝贵的时间和想法!

Answer 1

这将删除具有相同状态的其他行并分配一个序列号:

with cte as 
 (
   select *,
      lead(Operation_Name) -- previous Operation_Name
      over (partition by case_number
            order by Dat desc) as prev_op
   from tab
 )
select cte.*, 
   datediff(day
           ,date
           ,lag(date) -- next date
            over (partition by case_number
                  order by Date desc)
           ) as days_between,
   row_number()
   over (partition by case_number
         order by Date desc) as rn
from cte
where prev_op <> Operation_Name -- different value
   or prev_op is null           -- or first row
;

现在您可以将其传递给 PIVOT 或执行旧式 max(case):

with cte as 
 (
   select *,
      lead(Operation_Name) -- previous Operation_Name
      over (partition by case_number
            order by Date desc) as prev_op
   from tab
 )
, cte2 as
 (
   select cte.*, 
      datediff(day
              ,date
              ,lag(date) -- previous Operation_Name
               over (partition by case_number
                     order by Date desc)
) as days_between,
      row_number()
      over (partition by case_number
            order by Date desc) as rn
   from cte
   where prev_op <> Operation_Name -- different value
      or prev_op is null           -- or first row
 )
select case_number,
   max(case when rn = 1 then Operation_Name end),
   max(case when rn = 2 then Operation_Name end),
   max(case when rn = 2 then days_between end),
   max(case when rn = 3 then Operation_Name end),
   max(case when rn = 3 then days_between end),
   max(case when rn = 4 then Operation_Name end),
   max(case when rn = 4 then days_between end)
from cte2
group by case_number