I need to build a history considering the priority. This means, whenever more than one product is active at the same time only the product with the highest priority will be considered (but there could as well be more than one products with the same "highest" priority active). The desired result for the above example would be:
As you see, the two priority "1" products are active at the same time because both of them have the highest priority.
Has anybody an idea how to solve this problem. I tried a solution joining the sys_calendar.calendar table, expanding the historical records to have one record for each day, afterwards considering the highest priority per day and selecting for each day only the records with the highest priority. And packing the history afterwards again with OLAP functions but it was too slow (the table has 200 Mio records).
Any idea how to solve this (maybe with OLAP functions)?
Thank you for supporting me. I tried to use your solution and it worked quite well but in one special case there seems to be a problem:
If the records look like this:
i lost the inner part I marked with '--'
Here is a solution i found using OLAP functions. The outer part of the query does nothing else than packing together consecutive records with same priority (having been created by the inner algorithm):
from ( select customer_id,product,start_date,end_date,priority,product_id ,sum(changed) over (partition by t01.customer_id,product_id order by t01.start_date ROWS UNBOUNDED PRECEDING) - changed as group_cd
from ( select customer_id,product,start_date,end_date,priority,product_id ,max(t01.start_date) over ( partition by t01.customer_id,t01.product_id order by t01.product,t01.start_date ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as start_date_next ,case when ((end_date+1) <> start_date_next) then 1 else 0 end Changed
from( select customer_id,product,priority,date_x+1-is_start_date as start_date, max(is_start_date) over (partition by customer_id,product_id order by date_x rows between 1 following and 1 following) as next_is_start_date, max(date_x) over (partition by customer_id,product_id order by date_x rows between 1 following and 1 following) - next_is_start_date as end_date ,product_id
select t01.customer_id,date_x,t02.product,t02.priority,is_start_date,t02.product_id from (select customer_id,start_date as date_x ,1 as is_start_date,product_id FROM twh_rpr.roaming_product_base4
select customer_id,end_date,0 as is_start_date,product_id FROM twh_rpr.roaming_product_base4
inner join twh_rpr.roaming_product_base4 t02 on t01.customer_id = t02.customer_id and date_x between t02.start_date and t02.end_date ) t01 t01a qualify priority = min(priority) over (partition by customer_id,start_date) WHERE end_date IS NOT NULL AND start_date <= end_date
) t01 ) t01 ) t01 group by t01.customer_id,t01.product,t01.priority,t01.product_id,group_cd