Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Nandika.

Asked: March 02, 2017 - 4:52 am UTC

Answered by: Chris Saxon - Last updated: August 14, 2020 - 9:30 am UTC

Category: SQL*Plus - Version: 11 g

Viewed 1000+ times

You Asked

Hi All,

I would like to get the gaps between date range and needed it to separate the gaps by MM ID.

could you please someone help me to resolve below

INPUT:

ID | MM Number | From | To
12345|4444444 |2015-01-03 |2015-02-02
12346|4444444 |2015-02-03 |2015-03-02

<< missing one record ( ex same MM Number no record for period from 2015-03-03 to 2015-04-02

12347|4444444 |2015-04-03 |2015-05-01
12348|4444444 |2015-05-02 |2015-06-01
12349|4444444 |2015-06-02 |2015-07-01
12310|4444444 |2015-07-02 |2015-07-31

<< missing one record (ex: same MM Number no record for period from 2015-08-01 to 2015-08-31

12310|4444444|2015-09-01|2015-09-30



Expected Out put:
MM No | Missing Start Date | Missing To Date
4444444 |2015-03-03 |2015-04-02
4444444 |2015-08-01 |2015-08-31

I've a table with below data

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','01/03/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');

and we said...

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

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Or a concise solution from Antony Boucher in 2007

March 02, 2017 - 3:26 pm UTC

Reviewer: Stew Ashton from Paris, France

See https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:529176000346581356#533640800346285607
select * from (
  select nmi, 
    max(invoice_end_date) over(
      partition by nmi order by invoice_start_date
    ) + 1 start_gap,
    lead(invoice_start_date) over(
      partition by nmi order by invoice_start_date
    ) - 1 end_gap
  from icr_tmp
)
where start_gap <= end_gap;

NMI      START_GAP   END_GAP
4444444  2015-03-03  2015-04-02
4444444  2015-08-01  2015-08-31

Chris Saxon

Followup  

March 02, 2017 - 4:51 pm UTC

Nice. Thanks for digging that out Stew.

March 02, 2017 - 11:12 pm UTC

Reviewer: Nandika Kodituwakku

This works great in Oracle, but if I run same SQL in Netezza i'm getting 0 records as output. I'm sorry if this not relevant to this forum.

Anyway thank you all for the help
Connor McDonald

Followup  

March 03, 2017 - 5:28 am UTC

'connect by' is an Oracle construct, we've used in this instance to generate rows, eg

SQL> select rownum from dual connect by level <= 10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> select rownum from dual connect by level <= 5;

    ROWNUM
----------
         1
         2
         3
         4
         5



You'll need to find the equivalent in Netezza

Works awesome.

August 02, 2019 - 5:48 pm UTC

Reviewer: A reader

This works great. You Rock..!!
Connor McDonald

Followup  

August 05, 2019 - 9:08 am UTC

Glad we could help

But what about values that are not discrete?

August 14, 2020 - 1:54 am UTC

Reviewer: Richard from NM USA

Thanks, that solution is very helpful for discrete variables such as dates. But what about floating-point ranges? If I have a table that has floating-point start and end values for measurements of various objects, and I want to know where are the gaps in the measurements for each object, is there a way in SQL to accomplish that, without having to jump to PL/SQL? And what if there are overlaps in the ranges as well as gaps, and I want just the gaps? I could truncate the values at some decimal place to make them mimic discrete values, but there could be an enormous number of possible discrete values that way (billions), so I'm not sure that approach would work. So, say I have:

create table obj_measure (id VARCHAR2(15), measure_start FLOAT(53), measure_end FLOAT(53));
insert into obj_measure values ('11', 0, 123.456);
insert into obj_measure values ('11', 101.101, 1000000);
insert into obj_measure values ('22', 1.2345, 2345.678);
insert into obj_measure values ('22', 12345.6, 456789.12);
insert into obj_measure values ('22', 12345.67, 456789.1);
commit;


And I want results that look something like (object 11 has no gaps):
ID GAP_START GAP_END
22  0         1.2345
22  2345.678  12345.6
22  456789.12 1000000


How would I do that?

Chris Saxon

Followup  

August 14, 2020 - 9:30 am UTC

So you need all the gaps from zero up to some limit?

The solution from Stew above gets you most of the way there. Add the upper limit to the lead and you'll get the gaps within the data up to the max. It excludes zero -> the smallest value though.

You can get this by adding a (0, 0) start/end measurement for each id.

Here's a quick way to do that, there's probably a simpler method:

with ids as (
  select distinct id from obj_measure
), st as (
  select 0 st, 0 en from dual
), starts as (
  select * from ids cross join st
), vals as (
  select * from obj_measure
  union all
  select * from starts
), rws as (
  select id, 
         max ( measure_end ) over ( 
           partition by id 
           order by measure_start 
         ) mx,
         lead ( measure_start, 1, 1000000 ) over ( 
           partition by id 
           order by measure_start 
         ) nx
  from   vals 
)
  select * from rws 
  where  mx < nx;
  
ID    MX            NX        
22               0     1.2345 
22        2345.678    12345.6 
22       456789.12    1000000 

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.