The requirement here is To Eliminate Overlaps but also Take MIN and MAX + Distinct Combinations per CUST_EQP_CONTRACT_NUM(Project) Accordingly.
--create table1(EXPORT_TABLE) this is my actual data
create table EXPORT_TABLE(CUST_EQP_CONTRACT_NUM varchar2(150),EQP_ANT_BASE_HEIGHT_CALC number,EQP_ANT_TIP_HEIGHT_CALC number);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',166,174);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',167.52,172.48);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',168.53,171.48);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',99.69,108.31);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',102.69,111.31);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231,239);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231.5,238.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',232.5,240.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',233.58,239.82);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',98.98,101.02);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',118.98,121.02);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',138.5,161.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',148.5,171.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',255,263);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',256.64,261.36);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',4.2,5.2);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',3.9,4.9);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',2.1,4.8);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',6.2,6.8);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',5.9,6.5);
Not I will create another Table just to eliminate source rows and see how many distinct combinations are available for me in One PROJECT(CUST_EQP_CONTRACT_NUM)
create table export_table2
as
select distinct CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC from EXPORT_TABLE;
---DESIRED OUTPUT
CUST_EQP_CONTRACT_NUM EQP_ANT_BASE_HEIGHT_CALC EQP_ANT_TIP_HEIGHT_CALC
168903 166 174
283874 99.69 111.31
283874 231 240.5
507286 98.98 101.02
507286 118.98 121.02
507286 138.5 171.5
666905 255 263
168999 2.1 5.2
168999 5.9 6.8
--DB VERSION
select* from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
I Took a reference from
https://asktom.oracle.com/pls/apex/asktom.search?tag=date-intersection I Made this :
select CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC
from (
select * from EXPORT_TABLE2
where 1=1
model
partition by (CUST_EQP_CONTRACT_NUM)
dimension by (
row_number()
over(partition by CUST_EQP_CONTRACT_NUM order by EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC desc)
as rn
)
measures(EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC, 0 contained)
rules iterate(1E9) until(contained[iteration_number+1] is null) (
contained[rn > iteration_number+1] =
case when EQP_ANT_BASE_HEIGHT_CALC[cv()] <= EQP_ANT_TIP_HEIGHT_CALC[iteration_number+1]
then 1 else contained[cv()]
end
)
)
where contained = 0
order by 1,2; --If you compare this with Desired output from the below query it's not matching MIN and MAX, it eliminates overlaps but I want to tweak this solution
I made the below Solution not working for
CUST_EQP_CONTRACT_NUM 168999, Also can it be possible in MODEL Query?
--As in Source it's 8+ Million rows, so Performace wise we need to have a better solution.
with export_table as (select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from export_table2)
,tab as
(select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from export_table
where (CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC) not in (
select a.CUST_EQP_CONTRACT_NUM,b.EQP_ANT_BASE_HEIGHT_CALC, b.EQP_ANT_TIP_HEIGHT_CALC from export_table a
inner join export_table b on
(a.CUST_EQP_CONTRACT_NUM = b.CUST_EQP_CONTRACT_NUM and
((b.EQP_ANT_BASE_HEIGHT_CALC > a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_BASE_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC)
and
(b.EQP_ANT_TIP_HEIGHT_CALC > a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_TIP_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC))
))
)
,tab2 as
(select a.CUST_EQP_CONTRACT_NUM,a.EQP_ANT_BASE_HEIGHT_CALC, b.EQP_ANT_TIP_HEIGHT_CALC -- only overlap
from tab a
inner join tab b on (a.CUST_EQP_CONTRACT_NUM = b.CUST_EQP_CONTRACT_NUM
and b.EQP_ANT_BASE_HEIGHT_CALC >= a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_BASE_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC
and b.EQP_ANT_TIP_HEIGHT_CALC > a.EQP_ANT_TIP_HEIGHT_CALC))
select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from tab a
where (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC) not in (select CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC from tab2)
and (CUST_EQP_CONTRACT_NUM,EQP_ANT_TIP_HEIGHT_CALC) not in (select CUST_EQP_CONTRACT_NUM,EQP_ANT_TIP_HEIGHT_CALC from tab2)
union all
select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from tab2
order by 1,2,3
;