Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Find the date gaps and count

New RootsAug 27 2019 — edited Aug 27 2019

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
*/
This post has been answered by mNem on Aug 27 2019
Jump to Answer

Comments

Processing

Post Details

Added on Aug 27 2019
#sql-pl-sql
6 comments
459 views