Solved

How do identify a "break in coveage"

Posted on 2015-02-04
8
169 Views
Last Modified: 2015-02-05
I have a simple table in Oracle 11g that contains a date-range for a customer:

customerNumber integer
startDate date
endDate date

CUSTOMERNUMBER   STARTDATE   ENDDATE   
             1   2013-01-01  2013-12-31
             1   2014-01-01  2014-05-31
             1   2014-06-01  2014-12-31
             1   2015-01-02  2015-12-31
             2   2015-01-01  2015-01-31
             2   2015-03-01  2015-03-31

Open in new window


I would like to identify “breaks in coverage”. For example, customerNumber 1 had a one-day break in coverage between 2014-12-31 and 2015-01-02. Also, customerNumber 2 had a one-month break in coverage in February of this year. How would I best identify that?

Thanks in advance!
DaveSlash
0
Comment
Question by:daveslash
  • 4
  • 2
  • 2
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40589251
Try this:
select customernumber, (nextstartdate-enddate)-1 from (
      select customernumber, enddate, lead(startdate) over(partition by customernumber order by startdate) nextstartdate
      from simple_table
)
where nextstartdate-enddate > 1
/
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40589273
your data has discrete ranges

can they overlap?
0
 
LVL 18

Author Comment

by:daveslash
ID: 40589279
No. The ranges cannot overlap.
0
 
LVL 18

Author Closing Comment

by:daveslash
ID: 40589644
slightwv,

You are THE MAN! Thanks!

select *
  from MySchema.SimpleTable
;

CUSTOMERNUMBER STARTDATE ENDDATE  
-------------- --------- ---------
             1 01-JAN-13 31-DEC-13
             1 01-JAN-14 31-MAY-14
             1 01-JUN-14 31-DEC-14
             1 02-JAN-15 31-DEC-15
             2 01-JAN-15 31-JAN-15
             2 01-MAR-15 31-MAR-15
             3 02-JAN-15 31-DEC-15
             3 01-JAN-16 31-DEC-16

8 rows selected.

select customerNumber,
       startDate,
       endDate,
       (nextstartDate - endDate) - 1 as gap
  from (    select customernumber,
                   startDate,
                   enddate,
                   lead(startdate) over(partition by customernumber order by startdate) nextstartdate
              from MySchema.SimpleTable )
 where nextstartdate - enddate > 1
;


CUSTOMERNUMBER STARTDATE ENDDATE          GAP
-------------- --------- --------- ----------
             1 01-JUN-14 31-DEC-14          1
             2 01-JAN-15 31-JAN-15         28

2 rows selected.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Author Comment

by:daveslash
ID: 40591442
Thanks again, slightwv. I really do appreciate your help.

It even works the same using a WITH clause:

with IncludesTheNextOne as (
  select customernumber,
         startDate,
         enddate,
         lead(startDate) over(partition by customerNumber order by startDate) as nextStartDate
    from MySchema.SimpleTable )
select customerNumber,
       startDate,
       endDate,
       (nextstartDate - endDate) - 1 as theGap
  from IncludesTheNextOne
 where nextStartDate - endDate > 1
;

CUSTOMERNUMBER STARTDATE ENDDATE          GAP
-------------- --------- --------- ----------
             1 01-JUN-14 31-DEC-14          1
             2 01-JAN-15 31-JAN-15         28

2 rows selected.

Personally, I find the WITH clause to be slightly easier to read, but what do you think? Does it affect performance or readability?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40591523
Based on how it looks that Oracle implemented CTE, I don't think it matters.

Run execution plans on both and I bet you will find they are the same.

In case you don't know how:
explain plan for
select ...
/

Then to display the plan:
select * from table(dbms_xplan.display);


Both methods are basically inline views.

I would probably use CTE if I needed the same 'view' multiple times in the resulting query.

My SQL pre-dates CTE so my first choice is ALWAYS inline views without WITH.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40591532
For a single-use CTE, the optimizer will often (but not always) rewrite it internally as an inline view.

The other option is to materialize it into a temp table on the fly.
(The undocumented MATERIALIZE hint can help here, but not really recommended)

If you use reuse the CTE multiple times this materialization can be immensely helpful.

I too like WITH clauses as a readability thing too.  Modularizes the code nicely.
0
 
LVL 18

Author Comment

by:daveslash
ID: 40592397
Thanks again, gentlemen. The explain-plan comes out exactly the same for the two queries involved.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now