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
Solved

How do identify a "break in coveage"

Posted on 2015-02-04
8
185 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 74

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 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…

808 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