?
Solved

How do identify a "break in coveage"

Posted on 2015-02-04
8
Medium Priority
?
201 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:Dave Ford
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 74

Expert Comment

by:sdstuber
ID: 40589273
your data has discrete ranges

can they overlap?
0
 
LVL 18

Author Comment

by:Dave Ford
ID: 40589279
No. The ranges cannot overlap.
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
LVL 18

Author Closing Comment

by:Dave Ford
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
 
LVL 18

Author Comment

by:Dave Ford
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 77

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 74

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:Dave Ford
ID: 40592397
Thanks again, gentlemen. The explain-plan comes out exactly the same for the two queries involved.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

801 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