sql查询，以捕获最小日期，最大日期为基础的行排序高达空

41zrol4v 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(313)

议程是跟踪资源的招聘和再招聘场景

empID   emp_Status      CreateDate      end_date
1       active        **2019-02-01**Null
1       active          2019-02-02      2019-01-04 (Skipped because last day is greater than joining date)
1       active          2019-02-03      Null
1       Terminated      2019-02-04    **2019-02-02**
1       Terminated      2019-02-05      2019-02-02
1       active          2019-02-06      Null

输出应能够跟踪加入日期和最后一个工作日和各自的计数
输出：

empID   join_date       last_date       Joining_count
1       2019-02-01      2019-02-02      1
1       2019-02-06      Null            2

我需要在redshift或oraclesql查询中实现这一点。请帮我渡过难关。

sql Database amazon-redshift oracle amazon-rds

来源：https://stackoverflow.com/questions/62612039/sql-query-to-capture-minimum-date-max-date-based-on-row-ordering-upto-null

1条答案

按热度按时间

7fhtutme1#

如果我理解正确，对于每个“新”活动，您希望下一个“终止”。一种方法是创建反向计数为终止的组，然后进行聚合：

select empid,
       min(case when emp_status = 'active' then createdate end) as active_date,
       min(case when emp_status = 'Terminated' then createdate end) as terminate_date,
       row_number() over (partition by empid order by min(createdate)) as joining_count
from (select t.*,
             sum(case when emp_status = 'Terminated' then 1 else 0 end) over (partition by empid order by createdate desc) as grp
      from t
     ) t
group by empid, grp
having sum(case when emp_status = 'active' then 1 else 0 end) > 0;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-24

我来回答

sql查询，以捕获最小日期，最大日期为基础的行排序高达空

1条答案

相关问题

热门标签

最新问答