here is the date as an example
select 'part1' as part , 'supplierAAA' as supplier , 'order1' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part2' as part , 'supplierBBB' as supplier , 'order2' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('31-12-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part2' as part , 'supplierBBB' as supplier , 'order4' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order5' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order6' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order7' as order_name, 'G' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order8' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('15-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order9' as order_name, 'G' as order_type , to_date('16-12-2023','dd-mm-yyyy') as start_date , to_date('31-12-2024','dd-mm-yyyy') as end_date from dual
there are 2 types of order, E and G. I need to flag all orders which have for the same part and supplier and time frame a G order(s) covering an E order completely.
Because there are Millions of records I don't want to use self join and join the same data set which could be an option.
the simplest way would be to use window function for the same part,supplier and time frame to look if the max(order_type) = 'E' so in such case the order of order_type E has no corresponding E order.
for example
part1 with supplierAAA has just one order of order_type E so add Flag=0.
part2 with supplierBBB has E order and G order but the time frames doesn't match, thus Flag=0 both of them.
part3 with supplierCCC has 2 E orders , one in the first half of 2022 and one in 2023 full year. the E order is covered by G order in 2022 so Flag=1 both of them.
the E order in 2023 is covered by 2 G orders but it is covered also completely so Flag=1 all 3 orders as well.
the last case is the issue why window function will not work in partition by part,supplier,start_date,end_date , cause the dates are not aligned so it will fail to flag the orders with 1.
what option do I have? If using self join then in the ON clause I need to do something like E order start_date between G order start_date and G order end_date with OR condition to find out if the time frames fully overlap. this is not suitable to be fast enough.
here is the date as an example
select 'part1' as part , 'supplierAAA' as supplier , 'order1' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part2' as part , 'supplierBBB' as supplier , 'order2' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('31-12-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part2' as part , 'supplierBBB' as supplier , 'order4' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order5' as order_name, 'E' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order6' as order_name, 'E' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('31-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order7' as order_name, 'G' as order_type , to_date('01-01-2022','dd-mm-yyyy') as start_date , to_date('30-06-2022','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order8' as order_name, 'G' as order_type , to_date('01-01-2023','dd-mm-yyyy') as start_date , to_date('15-12-2023','dd-mm-yyyy') as end_date from dual
union all
select 'part3' as part , 'supplierCCC' as supplier , 'order9' as order_name, 'G' as order_type , to_date('16-12-2023','dd-mm-yyyy') as start_date , to_date('31-12-2024','dd-mm-yyyy') as end_date from dual
there are 2 types of order, E and G. I need to flag all orders which have for the same part and supplier and time frame a G order(s) covering an E order completely.
Because there are Millions of records I don't want to use self join and join the same data set which could be an option.
the simplest way would be to use window function for the same part,supplier and time frame to look if the max(order_type) = 'E' so in such case the order of order_type E has no corresponding E order.
for example
part1 with supplierAAA has just one order of order_type E so add Flag=0.
part2 with supplierBBB has E order and G order but the time frames doesn't match, thus Flag=0 both of them.
part3 with supplierCCC has 2 E orders , one in the first half of 2022 and one in 2023 full year. the E order is covered by G order in 2022 so Flag=1 both of them.
the E order in 2023 is covered by 2 G orders but it is covered also completely so Flag=1 all 3 orders as well.
the last case is the issue why window function will not work in partition by part,supplier,start_date,end_date , cause the dates are not aligned so it will fail to flag the orders with 1.
what option do I have? If using self join then in the ON clause I need to do something like E order start_date between G order start_date and G order end_date with OR condition to find out if the time frames fully overlap. this is not suitable to be fast enough.
Share Improve this question edited Feb 5 at 18:43 Dale K 27.3k15 gold badges56 silver badges83 bronze badges asked Feb 5 at 18:06 PatoPato 3111 silver badge10 bronze badges 2
e.start_time BETWEEN g.start_time AND g.end_time AND e.end_time BETWEEN g.start_time AND g.end_time– Barmar Commented Feb 5 at 18:29