<div>
Is your expected output correct for the given input? 
 
First thing I notice is the startdate of client 99. 
 
The output is 2008/02/06 &nbsp;but that date is nowhere in the inputs. 
 
Is there some other processing expected? &nbsp;Or are there errors (possibly others) in the example?
</div>

<div>
sdstuber, yes this was a typo. &nbsp;the start date on the output for client 99 should have read 01/02/2002 not 06/02/2008.
</div>

<div>
Thank you both for these excellent solutions. 
 
The other possibility which I didnt mention in the above problem, is that the episode was not ended so may be open as well as the plan. How would the code need to be amended to cope with this? 
 
Id want the output to be the client ID, the earliest start and latest end date of plans/ episodes which overlap(or if they dont overlap the date ranges of either the plan or episode), the date range type which the range covers (EP, EP/ PL or PL) and the duration of the range. 
 
Thanks again for you help with this. If you want me to add this as a new question Im happy to. 

</div>

<div>
Well, my solution was already handling &quot;open&quot; plan end dates. &nbsp;You'd just add something similar for the open episodes. &nbsp;The question would be if an &quot;open&quot; episode precedes a plan start date do you merge those or do you still only merge when the episode ends 1 or 0 days before the plan start? &nbsp;I've included both. &nbsp;I think it's close, but you can open another question if you really need help. &nbsp;Feel free to post the query that's closest to what you want in a related question - many people here can help you debug it. 
 
Good luck! 
 
 

<pre><code class="code-10 nolinks" id="code-121539">If you aren't merging &quot;open&quot; episodes and plans
 
select e.clientID, e.startdate,
 case when p.clientID is not null and 
 nvl(e.enddate,add_months(sysdate,1200)) between p.startdate-1 and p.startdate
 then nvl(p.enddate,sysdate)
 else e.enddate end enddate,
 case when p.clientID is not null and 
 nvl(e.enddate,add_months(sysdate,1200)) between p.startdate-1 and p.startdate
 then nvl(p.enddate,sysdate)
 else nvl(e.enddate,e.startdate) end - e.startdate days, -- WILL BE &quot;0&quot; FOR OPEN!!
 'EP' || case when p.clientID is not null and 
 nvl(e.enddate,add_months(sysdate,1200)) between p.startdate-1 and p.startdate
 then ',Pl' end Type
 from episodeTbl e, planTbl p
where e.clientID = p.clientID (+)
union all
select clientID, startDate, nvl(endDate, sysdate), nvl(endDate, sysdate) - startdate, 'Pl'
from planTbl p
where not exists (select 1 from episodeTbl e
 where clientID = p.clientID and
 nvl(e.enddate,add_months(sysdate,1200)) between p.startdate-1 and p.startdate)
 
If you are:
select e.clientID, e.startdate,
 case when p.clientID is not null and 
 nvl(e.enddate,p.startdate) between p.startdate-1 and p.startdate and
 e.startdate &lt; p.startdate
 then nvl(p.enddate,sysdate)
 else e.enddate end enddate,
 case when p.clientID is not null and 
 nvl(e.enddate,p.startdate) between p.startdate-1 and p.startdate and
 e.startdate &lt; p.startdate
 then nvl(p.enddate,sysdate)
 else nvl(e.enddate,e.startdate) end - e.startdate days, -- WILL BE &quot;0&quot; FOR OPEN!!
 'EP' || case when p.clientID is not null and 
 nvl(e.enddate,p.startdate) between p.startdate-1 and p.startdate and
 e.startdate &lt; p.startdate
 then ',Pl' end Type
 from episodeTbl e, planTbl p
where e.clientID = p.clientID (+)
union all
select clientID, startDate, nvl(endDate, sysdate), nvl(endDate, sysdate) - startdate, 'Pl'
from planTbl p
where not exists (select 1 from episodeTbl e
 where clientID = p.clientID and
 nvl(e.enddate,p.startdate) between p.startdate-1 and p.startdate and
 e.startdate &lt; p.startdate)
</code></pre>
</div>

<div>
<div class="content wysiwyg-content">
Please see the attached. &nbsp;Fig. 1 lists the raw data (two tables). Fig. 2 Shows the desired output. 
Id like the output to merge the tables together, displaying the clients earliest start date and latest end date of overlapping date ranges. Note that all Episodes will always have an end date of 01-04-2008 or before. If the end date of the Episode is the same date or a day before the start date of the plan I want these date ranges merged. If there is a gap of more than one day I want this to count as two date ranges. 
The tables are joined using the clientid fields. The start and end dates of a clients episode do not overlap, nor do the start and end dates of a clients plans. 
Any help is appreciated! 
 
<a href="https://filedb.experts-exchange.com/incoming/2008/07_w29/42098/Date-Range-Sample.xls" target="_blank" class="file-inline" title="Date range sample (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Date-Range-Sample.xls</a>
</div>
</div>

Date-Range-Sample.xls

Please see the attached.  Fig. 1 lists the raw data (two tables). Fig. 2 Shows the desired output.
Id like the output to merge the tables together, displaying the clients earliest start date and latest end date of overlapping date ranges. Note that all Episodes will always have an end date of 01-04-2008 or before. If the end date of the Episode is the same date or a day before the start date of the plan I want these date ranges merged. If there is a gap of more than one day I want this to count as two date ranges.
The tables are joined using the clientid fields. The start and end dates of a clients episode do not overlap, nor do the start and end dates of a clients plans.
Any help is appreciated! 


Compare date ranges between tables

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.