根据描述字段获取日期值

Question

我有一个名为actlog .I的表，其中包含每个特定案例的活动信息。现在我需要获取案例从一个团队转移到另一个团队的日期。

下面是示例。在这里，我需要获取案例从1级转移到2级的日期，即2/11/2018

Case    Team    Date    Agent   Summary
123 Level 2     2/13/2018   Ravi    Working on the case
123 Level 2     2/12/2018   Ravi    Working on the case
123 Level 2     2/11/2018   Ravi    Transferred to L2 : Agent Ravi
123 Level 1 2/10/2018   Ram Working on the case
123 Level 1 2/10/2018   Ram Working on the case
123 Level 1 2/10/2018   Ram Working on the case
123 Level 1 2/10/2018   Ram Working on the case

Answer 1

使用lag()

select t.*
from (select t.*, lag(team) over (partition by case order by date) as prev_team
      from t
     ) t
where prev_team <> team;

我更喜欢根据实际数据。您还可以尝试依赖于summary并使用like

select t.*
from t
where summary like 'Transferred to%';