?
Solved

How do identify a "break in coveage"

Posted on 2015-02-04
8
Medium Priority
?
211 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 78

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 78

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
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…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

621 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