tonMachine100
asked on
Compare date ranges between tables cont.
This problem is related to a question that was successfully answered a couple of days ago. Unfortunately the report requirements have since changed so I'm going to need a little more help.
The report now needs to display the earliest start and latest end date (or todays date if null) of plans/ episodes which overlap. If a clients episode or plan dates do not overlap, just the start and end date of the plan or episode need to be displayed (or todays date if no end date).
I've used the solution from the previous question as a basis to try and answer this but Im not quite getting the right results. I've attached the code i'm using. A brief exanation of this code- the first query gets the earliest start and latest end date of the overlapping episodes/ plans, and the start and end dates of episodes which do not have overlapping plans. The second query gets the start and end dates of plans which do not have overlapping episodes.
Also, please see the attached spreadsheet. fig 1 shows the two sample tables used, fig 2. the current output using the following code, and fig 3. the desired output. The second tab on the spreadsheet shows the create and populate statements for the sample data.
Thank you.
The report now needs to display the earliest start and latest end date (or todays date if null) of plans/ episodes which overlap. If a clients episode or plan dates do not overlap, just the start and end date of the plan or episode need to be displayed (or todays date if no end date).
I've used the solution from the previous question as a basis to try and answer this but Im not quite getting the right results. I've attached the code i'm using. A brief exanation of this code- the first query gets the earliest start and latest end date of the overlapping episodes/ plans, and the start and end dates of episodes which do not have overlapping plans. The second query gets the start and end dates of plans which do not have overlapping episodes.
Also, please see the attached spreadsheet. fig 1 shows the two sample tables used, fig 2. the current output using the following code, and fig 3. the desired output. The second tab on the spreadsheet shows the create and populate statements for the sample data.
Thank you.
select
clientid, startdate, enddate
from
(select
epi_client clientid,
(case
when o_plans.plan_client is not null and (EPI_START_DATE < PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) between PLAN_START_DATE and nvl (PLAN_END_DATE, sysdate)) then EPI_START_DATE
when o_plans.plan_client is not null and (EPI_START_DATE <= PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) >= nvl(PLAN_END_DATE, sysdate)) then EPI_START_DATE
when o_plans.plan_client is not null and (EPI_START_DATE between PLAN_START_DATE and nvl(PLAN_END_DATE, sysdate)) and (nvl(EPI_END_DATE, sysdate) > nvl(PLAN_END_DATE, sysdate)) then PLAN_START_DATE
when o_plans.plan_client is not null and (EPI_START_DATE > PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) < nvl(PLAN_END_DATE, sysdate)) then PLAN_START_DATE
else EPI_START_DATE
end) as startdate,
(case
when o_plans.plan_client is not null and (EPI_START_DATE < PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) between PLAN_START_DATE and nvl (PLAN_END_DATE, sysdate)) then PLAN_END_DATE
when o_plans.plan_client is not null and (EPI_START_DATE <= PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) >= nvl(PLAN_END_DATE, sysdate)) then EPI_END_DATE
when o_plans.plan_client is not null and (EPI_START_DATE between PLAN_START_DATE and nvl(PLAN_END_DATE, sysdate)) and (nvl(EPI_END_DATE, sysdate) > nvl(PLAN_END_DATE, sysdate)) then EPI_END_DATE
when o_plans.plan_client is not null and (EPI_START_DATE > PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) < nvl(PLAN_END_DATE, sysdate)) then PLAN_END_DATE
else EPI_END_DATE
end) as enddate
from
o_plans, o_episodes
where
o_plans.plan_client (+) = o_episodes.epi_client
union all
select plan_client clientid, plan_start_Date planstart, plan_end_Date planend
from o_plans
where not exists (select 1 from o_episodes
where epi_client = plan_client
and ((EPI_START_DATE < PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) between PLAN_START_DATE and nvl (PLAN_END_DATE, sysdate))
or (EPI_START_DATE <= PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) >= nvl(PLAN_END_DATE, sysdate))
or (EPI_START_DATE between PLAN_START_DATE and nvl(PLAN_END_DATE, sysdate)) and (nvl(EPI_END_DATE, sysdate) > nvl(PLAN_END_DATE, sysdate))
or (EPI_START_DATE > PLAN_START_DATE) and (nvl(EPI_END_DATE, sysdate) < nvl(PLAN_END_DATE, sysdate)))))
order by clientid, startdate
Last Comment
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.
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 great. Thankyou both for your help. There is one other requeirment i need from this report . I've put the details of this into a comment of the question.
ASKER
One last issue, could the code be further ameneded to cope with contiguos episodes/ plans if there was a break of a 1 day between the end of one date range and the start of another? At the moment, if an episode/plan is ended on the same day that a plan/episode is started, this would be treated as an overlap and the date durations would be added (which is just what i need), but if, for example a plan was ended on the 01-01-08 and an episode started on the 02-01-08 (or vise versa) this should be treated as one date range. Can this be incorporated into the code?
I see what you mean: there is no day in between, so it's continuous.
Look for the conditions using >= or <=, for example:
EPI_START_DATE<=PLAN_END_D
This selects episodes starting before or at the last day of the plan. To extend by one day, replace it with:
EPI_START_DATE<=PLAN_END_D
For the reverse tests, use -1 or add the day on the left side. The places where I used just > or < do not need to be changed.
I summarized the logic below (without the null cases, however).
(°v°)
Look for the conditions using >= or <=, for example:
EPI_START_DATE<=PLAN_END_D
This selects episodes starting before or at the last day of the plan. To extend by one day, replace it with:
EPI_START_DATE<=PLAN_END_D
For the reverse tests, use -1 or add the day on the left side. The places where I used just > or < do not need to be changed.
I summarized the logic below (without the null cases, however).
(°v°)
plan: - - - - - a = = = = b - - - - -
episodes: - c = d - - - - - - - - - - - - -- too early
- - c = d - - - - - - - - - - - -- contiguous, extend
- - - - - - - c = d - - - - - - -- overlap, ignore
- - - - - - - - - - c = d - - - -- overlap, extend
- - - - - - - - - - - - c = d - -- too late
strict overlap: d >= a and c <= b
or contiguous: d >= a-1 and c <= b+1
extend: d >= a-1 and c < a or d > b and c <= b+1
(respectively extend left and extend right)
ASKER
thanks for your suggestion and an explanation of how to deal with contiguous date ranges. I've amended the code and this seems to work! The code i am now using is below.
I've ran the code against our main data set (which contains approx 390 plans and 270 episodes), and as you may expect, contains a large variety of possible start and end dates for both plans and episodes! The code has worked very well, however in the cases of just two clients, two separate date ranges were listed for plans/ episodes which overlapped. I expected only one date range for each client. Ive attached the data from both tables which relate to one of the clients. Would the code need to be modified to deal with date combinations like the attached?
thanks again for your help.
I've ran the code against our main data set (which contains approx 390 plans and 270 episodes), and as you may expect, contains a large variety of possible start and end dates for both plans and episodes! The code has worked very well, however in the cases of just two clients, two separate date ranges were listed for plans/ episodes which overlapped. I expected only one date range for each client. Ive attached the data from both tables which relate to one of the clients. Would the code need to be modified to deal with date combinations like the attached?
thanks again for your help.
select
PLAN_CLIENT as client,
nvl((
select min(EPI_START_DATE)
from o_episodes
where EPI_CLIENT=PLAN_CLIENT
and (EPI_START_DATE<PLAN_START_DATE)
and (EPI_END_DATE +1 >=PLAN_START_DATE or EPI_END_DATE is null)
), PLAN_START_DATE) as start_date,
nvl(nvl((
select max(nvl(EPI_END_DATE,sysdate))
from o_episodes
where EPI_CLIENT=PLAN_CLIENT
and (EPI_START_DATE<=PLAN_END_DATE +1 or PLAN_END_DATE is null)
and (EPI_END_DATE>PLAN_END_DATE or EPI_END_DATE is null)
), PLAN_END_DATE), sysdate) as end_date,
'p' as o
from o_plans
union all
select
EPI_CLIENT,
EPI_START_DATE,
nvl(EPI_END_DATE,sysdate),
'e' as o
from o_episodes
where not exists (
select 1 from o_plans
where PLAN_CLIENT=EPI_CLIENT
and (PLAN_START_DATE <=EPI_END_DATE +1 or EPI_END_DATE is null)
and (PLAN_END_DATE + 1 >=EPI_START_DATE or PLAN_END_DATE is null))
I almost mentioned that fact in my first comment. The code obviously extends plans by using episodes. You could also do the reverse: extend episodes using plans, in which case the particular case you mention would return the "expected output", but it would then break in the reverse case (two episodes overlapping the same plan).
What you are expecting is a full merge of overlapping ranges. See the diagram below.
Since Oracle supports recursive SQL, there might be a way to do that in a single query. The basic idea would be:
* union plans and episodes (they are no longer different)
* sort them by ascending start date and ID
* select those that have no overlap with a record below in that sort order
* recursively extend the range for all overlapping records
The non-recursive solution would be:
* create a new table with the union of plans and episodes
* identify two overlapping records
* extend the first and delete the second
* repeat until no overlaps remain
In any case, you should ask a new question at this point, and make sure to mention "recursive SQL" in the title if you want to start on that new (and fascinating) path. I have no idea if either idea would work in Oracle, mind you.
(°v°)
What you are expecting is a full merge of overlapping ranges. See the diagram below.
Since Oracle supports recursive SQL, there might be a way to do that in a single query. The basic idea would be:
* union plans and episodes (they are no longer different)
* sort them by ascending start date and ID
* select those that have no overlap with a record below in that sort order
* recursively extend the range for all overlapping records
The non-recursive solution would be:
* create a new table with the union of plans and episodes
* identify two overlapping records
* extend the first and delete the second
* repeat until no overlaps remain
In any case, you should ask a new question at this point, and make sure to mention "recursive SQL" in the title if you want to start on that new (and fascinating) path. I have no idea if either idea would work in Oracle, mind you.
(°v°)
plans: - a = b - e = = f - - i = = j - note: three plans
episodes: - - - c = d - g = = h - k = l -
combined: - x = = = = = = = = = = = = y -
currently - a = = = d - - - - - - - - - - extended three plans
- - - c = = = = = = h - - - - -
- - - - - - - g = = = = = = j -
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
CREATE A VIEW ON EXISTING SQL SAY VIEW_X
and try this sql
select DISTINCT CLIENT_ID,STARTDATE,ENDDAT
MINUS
select a.CLIENT_ID,max(a.STARTDAT
VIEW_X a, VIEW_X b where a.pid=b.pid and a.STARTDATE>=b.STARTDATE and a.ENDDATE<=b.ENDDATE and a.rowid<>b.rowid
group by a.CLIENT_ID ;