Hi Team,
Could you please have a look ate below scenario and help me with building SQL please..
If there are intersecting date range, rows which has longer date interval should get returned.
i.e. One of the record for order 1 has date interval from 01/01/2017 to 01/12/2017,
however another order1 row exists wwith interval from 01/04/2017 to 01/12/2017 which falls under previous intrval i.e. 01/01/2017 to 01/12/2017.
in this case order1 with inerval 01/01/2017 to 01/12/2017 gets returned and should exclude interval 01/04/2017 to 01/12/2017.
NAME EFF_FROM_DT EFF_TO_DT
------ ----------- ----------
order1 01/01/2017 01/12/2017
order1 01/01/2018 01/12/2018
order2 01/01/2017 01/12/2017
order2 01/01/2018 01/12/2018
-------------- Sample table and data ------------------
CREATE TABLE TAB1
(NAME VARCHAR2(10 BYTE),
EFF_FROM_DT DATE,
EFF_TO_DT DATE
);
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-JAN-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-APR-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order1',to_date('01-JAN-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-JAN-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-APR-17','DD-MON-RR'),to_date('01-DEC-17','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-JAN-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));
Insert into TAB1 (NAME,EFF_FROM_DT,EFF_TO_DT) values ('order2',to_date('01-AUG-18','DD-MON-RR'),to_date('01-DEC-18','DD-MON-RR'));
COMMIT;
So you only want to show ranges where there is one row with a date falling in that range for the order?
If so, you can do something like:
- Generate all the possible dates between the start/end
- Join these to your table where the date falls between the start/end for each row
- Return those dates where there is only one date for that order
- Use the Tabibitosan method on the result of this to put these into groups
- Return the min & max date for each group
Which gives something like:
with min_max as (
select min ( eff_from_dt ) first_start,
max ( eff_to_dt ) last_end
from tab1
), all_dates as (
select first_start + level - 1 dt
from min_max
connect by level <= ( last_end - first_start + 1 )
), exclusions as (
select name, dt, count (*)
from tab1 t
join all_dates
on dt between eff_from_dt and eff_to_dt
group by name, dt
having count (*) = 1
), grps as (
select name,
dt,
dt - row_number () over ( partition by name order by dt ) grp
from exclusions
)
select name, min ( dt ), max ( dt )
from grps
group by name, grp
order by name, grp;
NAME MIN(DT) MAX(DT)
order1 01-JAN-2017 31-MAR-2017
order1 01-JAN-2018 01-DEC-2018
order2 01-JAN-2017 31-MAR-2017
order2 01-JAN-2018 31-JUL-2018 There are other ways you can solve this problem. There are 13 ways you can classify two date ranges in terms of how they overlap. How you classify which ranges overlap determines your solution.
Stew Ashton has written many blog posts on this topic. I recommend you read them:
https://stewashton.wordpress.com/2015/03/21/join-tables-on-date-ranges/ https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps/ https://stewashton.wordpress.com/tag/date-ranges-2/