I am getting stuck on this, hard to explain to write down here as well the subject heading hopefully by the data example and output result below that enough to give you an idea.

I am run out which Oracle SQL statement best to use. The over (partition) seem didn't work out for the below scenario.

screenshoot - before & after

DATA

The output that I want result like this:

I hope those makes sense for you and hopefully you can assist me on this.

Thank you very much

CodePudding user response：

You have to apply Tabibitosan algorithm first:

select customer_id, type, rn, min(dat) as min_date, max(dat) as max_dat
from (
    select d.*, row_id 1-row_number() over(partition by customer_id, type order by row_id) as rn 
    from data d
)
group by customer_id, type, rn
order by customer_id, type
;

CUST TYPE          RN MIN_DATE MAX_DAT 
---- ----- ---------- -------- --------
LB01 CAR 1         10 18/01/00 14/03/00
LB01 CAR 1         17 16/10/01 14/03/22
LB01 CAR 2          6 24/02/99 24/12/99
LB01 CAR 2         12 24/04/01 10/08/01
LB01 CAR 3         12 20/10/00 13/03/01
LB01 CAR 4          1 06/08/98 19/02/99

CodePudding user response：

refer to the above post made by @p3consulting regarding the Tabibitosan algorithm

here is the below script that you can copy and paste into your Oracle SQL to learn

for my question here still does not work for my data and still searching the right script

with
sample_data (customer_id, row_id, mock_date, product_type) as (
select 'LB01', '22', to_date ('03/14/22', 'mm/dd/rr'), 'CAR 1' from dual union all    
select 'LB01', '21', to_date ('03/11/22', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '20', to_date ('11/09/01', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '19', to_date ('10/16/01', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '18', to_date ('08/10/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '17', to_date ('05/29/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '16', to_date ('04/24/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '15', to_date ('03/13/01', 'mm/dd/rr'), 'CAR 3' from dual union all    
select 'LB01', '14', to_date ('12/21/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '13', to_date ('11/13/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '12', to_date ('10/20/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '11', to_date ('03/14/00', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '10', to_date ('01/18/00', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '09', to_date ('12/24/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '08', to_date ('09/14/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '07', to_date ('03/30/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '06', to_date ('02/24/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '05', to_date ('02/19/99', 'mm/dd/rr'), 'CAR 4' from dual union all    
select 'LB01', '04', to_date ('12/15/98', 'mm/dd/rr'), 'CAR 4' from dual union all    
select 'LB01', '03', to_date ('12/15/98', 'mm/dd/rr'), 'CAR 4' from dual union all
select 'LB01', '02', to_date ('09/24/98', 'mm/dd/rr'), 'CAR 4' from dual union all
select 'LB01', '01', to_date ('06/08/98', 'mm/dd/rr'), 'CAR 4' from dual 
)
 
select
customer_id ,
product_type ,
grp ,
min(mock_date) as min_date , 
max(mock_date) as max_date 
from (
select d.* ,
row_id   1 - row_number () over (partition by customer_id, product_type order by row_id) as grp
from sample_data d )
 
group by
customer_id ,
product_type ,
grp 
 
order by
grp desc
 

ENJOY!!