add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table project_sprint(project_id, sprint_id, est_task_per_sprint, start_date, end_date) as
select 1, 1, 262, date '2019-01-01', date '2019-01-31' from dual
union all select 1, 11, 263, date '2019-02-01', date '2019-02-28' from dual
union all select 1, 21, 266, date '2019-03-01', date '2019-03-31' from dual
union all select 1, 31, 262, date '2019-04-01', date '2019-04-30' from dual
union all select 1, 41, 261, date '2019-05-01', date '2019-05-31' from dual;
5 rows affected
SELECT project_id,
sprint_id,
est_task_per_sprint as hours,
start_date,
end_date,
sum(est_task_per_sprint) over (partition by project_id) as total,
coalesce(sum(est_task_per_sprint)
over (partition by project_id order by end_date), 0) as completed,
sum(est_task_per_sprint) over (partition by project_id)
- coalesce(sum(est_task_per_sprint)
over (partition by project_id order by end_date), 0) as remaining
FROM project_sprint
--WHERE project_id=1
ORDER BY project_id, start_date;
PROJECT_ID SPRINT_ID HOURS START_DATE END_DATE TOTAL COMPLETED REMAINING
1 1 262 01-JAN-19 31-JAN-19 1314 262 1052
1 11 263 01-FEB-19 28-FEB-19 1314 525 789
1 21 266 01-MAR-19 31-MAR-19 1314 791 523
1 31 262 01-APR-19 30-APR-19 1314 1053 261
1 41 261 01-MAY-19 31-MAY-19 1314 1314 0
SELECT project_id,
sprint_id,
est_task_per_sprint as hours,
start_date,
end_date,
coalesce(sum(est_task_per_sprint)
over (partition by project_id order by end_date
rows between 1 following and unbounded following), 0) as remaining
FROM project_sprint
--WHERE project_id=1
ORDER BY project_id, start_date;
PROJECT_ID SPRINT_ID HOURS START_DATE END_DATE REMAINING
1 1 262 01-JAN-19 31-JAN-19 1052
1 11 263 01-FEB-19 28-FEB-19 789
1 21 266 01-MAR-19 31-MAR-19 523
1 31 262 01-APR-19 30-APR-19 261
1 41 261 01-MAY-19 31-MAY-19 0