Solved

How do identify a "break in coveage"

Posted on 2015-02-04
8
193 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 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

691 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