Hi ,
Below is the table structure and sample data . I just want to find the the number of project unassigned periods for the employee. Could you please help on writing the query to fetch the expected result ?
with t(id,emp_code,projectcode,dates)
as (select 1,100,'p1',to_date('10-jan-2019') from dual union all
select 2,100,'p1',to_date('11-jan-2019') from dual union all
select 3,100,'p1',to_date('12-jan-2019') from dual union all
select 4,100,null,to_date('13-jan-2019') from dual union all
select 5,100,null,to_date('14-jan-2019') from dual union all
select 6,100,'p2',to_date('15-jan-2019') from dual union all
select 7,100,'p2',to_date('16-jan-2019') from dual union all
select 8,100,null,to_date('17-jan-2019') from dual union all
select 9,100,null,to_date('18-jan-2019') from dual union all
select 10,100,'p2',to_date('19-jan-2019') from dual union all
select 10,100,null,to_date('20-jan-2019') from dual)
select * from t;
/*---------------------------
Below is my Expected result
Emp_code start_dt end_dt count
----------------------------------------------
100 13-JAN-19 14-JAN-19 2
100 16-JAN-19 17-JAN-19 2
100 20-JAN-19 20-JAN-19 1
*/