Here's one way to do this:
- Generate a list of all the dates in your range
- Use this to find those dates not covered by a start/end period in your table:
alter session set nls_date_format = 'dd/mm/yyyy';
create table icr_tmp (
ID VARCHAR2(15),
NMI VARCHAR2(50),
INVOICE_START_DATE DATE,
INVOICE_END_DATE DATE
);
insert into icr_tmp
values('12345','4444444','03/01/2015','02/02/2015');
insert into icr_tmp
values('12346','4444444','03/02/2015','02/03/2015');
insert into icr_tmp
values('12347','4444444','03/04/2015','01/05/2015');
insert into icr_tmp
values('12348','4444444','02/05/2015','01/06/2015');
insert into icr_tmp
values('12349','4444444','02/06/2015','01/07/2015');
insert into icr_tmp
values('12310','4444444','02/07/2015','31/07/2015');
insert into icr_tmp
values('12310','4444444','01/09/2015','30/09/2015');
with dates as (
select date'2015-01-03'+level-1 dt from dual
connect by level <= (date'2015-08-31' - date'2015-01-03'+1)
), missing_dts as (
select dt
from dates
where not exists (
select null from icr_tmp
where dt between INVOICE_START_DATE and INVOICE_END_DATE
)
)
select * from missing_dts;
DT
03/03/2015
04/03/2015
05/03/2015
06/03/2015
07/03/2015
08/03/2015
...
To convert this to missing start/end periods, use the Tabibitosan method. This puts the dates into continuous groups by:
- Assigning a row_number() to each each
- Subtracting this rownum from the current date
This gives continuous values the same value. So all you need to do now is group by this calculated column and return the min and max dates:
with dates as (
select date'2015-01-03'+level-1 dt from dual
connect by level <= (date'2015-08-31' - date'2015-01-03'+1)
), missing_dts as (
select dt,
dt - row_number() over (order by dt) grp
from dates
where not exists (
select null from icr_tmp
where dt between INVOICE_START_DATE and INVOICE_END_DATE
)
)
select grp, min(dt), max(dt) from missing_dts
group by grp;
GRP MIN(DT) MAX(DT)
02/03/2015 03/03/2015 02/04/2015
30/06/2015 01/08/2015 31/08/2015
For a more detailed description of Tabibitosan, watch:
https://www.youtube.com/watch?v=yvimYixXo2Q