sql - Cumulative Calculation - Based on Sample Data

Question

Hi I am providing Sample Data below: Based on Sample data I want result output as per screenshot ..

create table mytable 
(
    emp_name varchar2(50)
    ,dept_number number
    ,Salary number
    ,Calendar_Year number
    ,Calendar_Period number
);

insert into mytable values ('Bob',6620,6500,2020,1);
insert into mytable values ('Bob',6620,1500,2020,1);
insert into mytable values ('Rachel',6620,3400,2020,1);
insert into mytable values ('Nancy',6620,1400,2020,1);
insert into mytable values ('Rachel',6620,4500,2020,2);
insert into mytable values ('Nancy',6620,2300,2020,2);
insert into mytable values ('Nancy',6620,1500,2020,3);
insert into mytable values ('Bob',6640,1200,2020,1);
insert into mytable values ('Bob',6640,1000,2020,1);
insert into mytable values ('Rachel',6640,1500,2020,1);
insert into mytable values ('Nancy',6640,1000,2020,1);
insert into mytable values ('Rachel',6640,4500,2020,2);
insert into mytable values ('Nancy',6640,1100,2020,2);
insert into mytable values ('Nancy',6640,1500,2020,3);

Answer 1

Hmmm . . . You seem to want to expand the rows and then take the most recent salary if it is not available:

select emp_name, dept_number, calendar_year, calendar_period,
       coalesce(sum(t.salary),
                lag(sum(t.salary) ignore nulls) over (partition by emp_name order by calendar_year, calendar_period)
                ) as salary
from (select distinct emp_name, dept_number from mytable) ed cross join
     (select distinct calendar_year, calendar_period from mytable) yp left join
     mytable t
     using (emp_name, dept_number, calendar_year, calendar_period)
group by emp_name, dept_number, calendar_year, calendar_period
order by calendar_year, calendar_period, dept_number, emp_name

Here is a db<>fiddle.