Member_2_2484401
asked on
How do identify a "break in coveage"
I have a simple table in Oracle 11g that contains a date-range for a customer:
customerNumber integer
startDate date
endDate date
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No. The ranges cannot overlap.
ASKER
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.
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.
ASKER
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?
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?
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.
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.
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.
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.
ASKER
Thanks again, gentlemen. The explain-plan comes out exactly the same for the two queries involved.
can they overlap?