CODEWITHSUNDEEP
sqldatabaseoracle-databaseamazon-redshiftamazon-rds
Suhas C
Suhas C

Reputation: 9

Sql query to capture minimum date, max date based on row ordering upto null

Agenda is to track hiring and rehiring scenario for a resource

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

Output should able to track joining date and last workday and respective count

output:

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

I need to achieve this in redshift or oracle sql query. Please help me through this.

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If I understand correctly, for each "new" active, you want the next "terminated". One method is to create groups with a reverse count of terminated and then aggregation:

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;

Here is a db<>fiddle.

Upvotes: 1

Related Questions