tonMachine100
asked on
Recursive Sql - date ranges between tables
The problem is associated with the merging of date ranges that exist in two tables, Episodes and Plans. The solution needs to produce a full merge of overlapping ranges for these dates (by client).
The attachment shows, fig 1 the two sample tables used, fig 2. the desired output. The second tab on the spreadsheet shows the create and populate statements for the sample data.
Each client may have a number of plans or episodes. The date ranges of a clients plans will not overlap, nor will the date ranges of a clients episodes. It is possible however for the dates of a clients plans and episodes to overlap.
The tables are linked by the plan_client and epi_client field. Where there is no end date in the plan or episode, this indicates that the plan is still open.
Please see a previous question Ive asked which is related to this - 'Compare date ranges between tables cont.'. In this question, an expert suggested that it maybe possible to do this using recursive sql and provided some useful information regarding how this report could be structured.
thanks
date-range-sample4.xls
The attachment shows, fig 1 the two sample tables used, fig 2. the desired output. The second tab on the spreadsheet shows the create and populate statements for the sample data.
Each client may have a number of plans or episodes. The date ranges of a clients plans will not overlap, nor will the date ranges of a clients episodes. It is possible however for the dates of a clients plans and episodes to overlap.
The tables are linked by the plan_client and epi_client field. Where there is no end date in the plan or episode, this indicates that the plan is still open.
Please see a previous question Ive asked which is related to this - 'Compare date ranges between tables cont.'. In this question, an expert suggested that it maybe possible to do this using recursive sql and provided some useful information regarding how this report could be structured.
thanks
date-range-sample4.xls
Last Comment
This requires splitting up the problem into its components. (are we no longer merging when it's the day before the next range?)
First, where there is an overlap, return the earliest start and the latest end.
select e.epi_client,
min(least(e.epi_start_date,p.plan_start_date)) startdate,
case when max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120))),
nvl(p.plan_end_date,add_months(trunc(sysdate),120))) =
add_months(trunc(sysdate),120)) then
'-'
else
to_char(max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120))),
nvl(p.plan_end_date,add_months(trunc(sysdate),120))),'DD-MON-YYYY') end enddate
from o_episodes e, o_plan p
where e.epi_client = p.plan_client and
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
)
--
-- Now UNION ALL the non-overlapping
--
union all
select epi_client, epi_start_Date, nvl(to_char(epi_end_Date,'DD-MON-YYYY'), '-')
from o_episodes e
where not exists (select 1 from o_plan p
where epi_client = p.plan_client and
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
))
union all
select plan_client, plan_start_Date, nvl(to_char(plan_end_Date,'DD-MON-YYYY'), '-')
from o_plan p
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
)
)
Good luck!
ASKER
Yes, sorry if this was not clear. If a date range finishes on the same day, or the day before the next one starts, the ranges needs to be merged.
Ok, it's really a minor change:
select e.epi_client,
min(least(e.epi_start_date,p.plan_start_date)) startdate,
case when max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120))),
nvl(p.plan_end_date,add_months(trunc(sysdate),120))) =
add_months(trunc(sysdate),120)) then
'-'
else
to_char(max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120))),
nvl(p.plan_end_date,add_months(trunc(sysdate),120))),'DD-MON-YYYY') end enddate
from o_episodes e, o_plan p
where e.epi_client = p.plan_client and
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or p.plan_start_date - e.epi_end_date between 0 and 1 or e.epi_start_date - p.plan_end_date between 0 and 1
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
)
--
-- Now UNION ALL the non-overlapping
--
union all
select epi_client, epi_start_Date, nvl(to_char(epi_end_Date,'DD-MON-YYYY'), '-')
from o_episodes e
where not exists (select 1 from o_plan p
where epi_client = p.plan_client and
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or p.plan_start_date - e.epi_end_date between 0 and 1 or e.epi_start_date - p.plan_end_date between 0 and 1
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
))
union all
select plan_client, plan_start_Date, nvl(to_char(plan_end_Date,'DD-MON-YYYY'), '-')
from o_plan p
(
e.epi_start_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or
e.epi_end_date between p.plan_start_date and nvl(p.plan_end_date,add_months(trunc(sysdate),120))
or p.plan_start_date - e.epi_end_date between 0 and 1 or e.epi_start_date - p.plan_end_date between 0 and 1
or
(e.epi_end_date is null and e.epi_start_date < p.plan_end_date)
or
p.plan_start_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
or
p.plan_end_date between e.epi_start_date and nvl(e.epi_end_date,add_months(trunc(sysdate),120))
)
)
Good luck!
ASKER
I'm getting an invalid number of arguments error when running the above. Its pointing to the max function in the case statement.
Yes, I dropped an ending parens (for the "greatest" or the "max") and had a misplaced one. And it's going to complain about the min, too. Here's an update of that section:
select e.epi_client,
min(least(e.epi_start_date,p.plan_start_date)) startdate,
case when max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120)),
nvl(p.plan_end_date,add_months(trunc(sysdate),120)))) =
add_months(trunc(sysdate),120)) then
'-'
else
to_char(max(greatest(nvl(e.epi_end_date,add_months(trunc(sysdate),120)),
nvl(p.plan_end_date,add_months(trunc(sysdate),120)))),'DD-MON-YYYY') end enddate
from o_episodes e, o_plan p
ASKER
I'm now getting a missing keyword error with this updated section.
The second union section works fine.
I'm getting a sql command not ended properly error with the third query.
The second union section works fine.
I'm getting a sql command not ended properly error with the third query.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This is spot on. Thankyou for your persistance.
Oracle Database
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.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
apart from that, this should be a good start:
Open in new window