SQL & PL/SQL

Hi All,

I got helped with similar issue before

Find missing data

Here is my data example using oracle 12c

create table cars(
    myId number,
    carId number,
    carClass number,
    partId number);
    
    insert into cars (myId, carId, carClass, partId)
    values (200, 1, 60, 11);
    insert into cars (myId, carId, carClass, partId)
    values (200, 2, 50, 16);
    insert into cars (myId, carId, carClass, partId)
    values (200, 3, 80, 14);
    
    create table myRates(
    myId  number,
    carId number,
    partId number,
    fy number,
    rate number);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,11,2020,2.3);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,11,2021,2.6);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,14,2020,3.3);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,14,2021,3.2);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,16,2020,1.2);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,1,16,2021,1.9);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,11,2020,2.1);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,11,2021,2.4);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,14,2020,3.1);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,14,2021,3.4);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,16,2020,1.1);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,2,16,2021,1.4);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,11,2020,3.1);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,11,2021,3.4);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,14,2020,3.31);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,14,2021,3.8);
    insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,16,2020,2.1);
     insert into myRates (myId,carId,partId,fy,rate)
    values (200,3,16,2021,2.8);
    
    create table myPerc(
    carId number,
    partId number,
    fy number,
    perc number);
    
    insert into myPerc (carId, partId, fy, perc)
    values (2,11,2020, 50); 
    insert into myPerc (carId, partId, fy, perc)
    values (2,11,2021, 100);
    with allYears as(
      select 200 as myId, 2020 + level - 1 fy 
      from dual
      connect by level <= 4 
      and prior dbms_random.value is not null
    )
    select y.myId, c.carId, c.carClass, c.partId, y.fy
    from allYears y
    left join cars c on c.myId = y.myId   
    order by c.carId, y.fy

The results

  
   
MYID
CARID
CARCLASS
PARTID
FY
1
200
1
60
11
2020
2
200
1
60
11
2021
3
200
1
60
11
2022
4
200
1
60
11
2023
5
200
2
50
16
2020
6
200
2
50
16
2021
7
200
2
50
16
2022
8
200
2
50
16
2023
9
200
3
80
14
2020
10
200
3
80
14
2021
11
200
3
80
14
2022
12
200
3
80
14
2023

Now when I try to use last_value I get wrong rate values

with allYears as(
      select 200 as myId, 2020 + level - 1 fy 
      from dual
      connect by level <= 4 
      and prior dbms_random.value is not null
    )
    select y.myId, c.carId, c.carClass, c.partId, y.fy
    ,last_value(r.rate ignore nulls) over (order by r.fy) as rate
    from allYears y
    left join cars c on c.myId = y.myId   
    left join myRates r partition by (r.myId) on r.carId = c.carId and r.partId = c.partId and r.fy = y.fy
    order by c.carId, y.fy

Results

   
MYID    CARID    CARCLASS    PARTID    FY    RATE
1    200    1    60    11    2020    3.31
2    200    1    60    11    2021    3.8
3    200    1    60    11    2022    3.8
4    200    1    60    11    2023    3.8
5    200    2    50    16    2020    3.31
6    200    2    50    16    2021    3.8
7    200    2    50    16    2022    3.8
8    200    2    50    16    2023    3.8
9    200    3    80    14    2020    3.31
10    200    3    80    14    2021    3.8
11    200    3    80    14    2022    3.8
12    200    3    80    14    2023    3.8

The correct data should be:

  
   
MYID
CARID
CARCLASS
PARTID
FY
RATE
PERC
1
200
1
60
11
2020
2.3
50
2
200
1
60
11
2021
2.6
100
3
200
1
60
11
2022
2.6
100
4
200
1
60
11
2023
2.6
100
5
200
2
50
16
2020
1.2
6
200
2
50
16
2021
2.1
7
200
2
50
16
2022
2.1
8
200
2
50
16
2023
2.1
9
200
3
80
14
2020
3.31
10
200
3
80
14
2021
3.8
11
200
3
80
14
2022
3.8
12
200
3
80
14
2023
3.8

Any help will be appreciate. Thanks!