clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591607 fiddles created (45714 in the last week).

with developer(id, name) as ( select 1, 'Dev1' from dual union all select 2, 'Dev2' from dual union all select 3, 'Dev3' from dual ), customer (id, name) as (select 1, 'Cust1' from dual), project(id, customer_id, name, type, start_date, end_date) as ( select 1, 1, 'P1', 2, date '2018-04-16', date '2018-06-02' from dual union all select 2, 0, 'P2', 0, date '2017-04-16', date '2017-04-20' from dual union all select 3, 1, 'P3', 0, date '2018-11-20', null from dual ), task(id, name, project_id, type, start_date, end_date) as ( select 1, 'T1', 1, 0, date '2018-06-01', date '2018-06-02' from dual union all select 2, 'T2', 2, 0, date '2017-04-17', date '2017-04-20' from dual union all select 3, 'T3', 3, 0, date '2018-11-21', null from dual ), developer_task(dev_id, task_id) as ( select 1, 1 from dual union all select 2, 2 from dual union all select 3, 2 from dual ) select p.id project_id, p.name project_name, d.name dev_name, case p.customer_id when 0 then 'Internal' else c.name end cust_name from project p left join customer c on c.id = p.customer_id join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id join developer d on d.id = dt.dev_id where p.end_date is not null
PROJECT_ID PROJECT_NAME DEV_NAME CUST_NAME
1 P1 Dev1 Cust1
2 P2 Dev2 Internal
2 P2 Dev3 Internal
 hidden batch(es)


with developer(id, name) as ( select 1, 'Dev1' from dual union all select 2, 'Dev2' from dual union all select 3, 'Dev3' from dual ), customer (id, name) as (select 1, 'Cust1' from dual), project(id, customer_id, name, type, start_date, end_date) as ( select 1, 1, 'P1', 2, date '2018-04-16', date '2018-06-02' from dual union all select 2, 0, 'P2', 0, date '2017-04-16', date '2017-04-20' from dual union all select 3, 1, 'P3', 0, date '2018-11-20', null from dual ), task(id, name, project_id, type, start_date, end_date) as ( select 1, 'T1', 1, 0, date '2018-06-01', date '2018-06-02' from dual union all select 2, 'T2', 2, 0, date '2017-04-17', date '2017-04-20' from dual union all select 3, 'T3', 3, 0, date '2018-11-21', null from dual ), developer_task(dev_id, task_id) as ( select 1, 1 from dual union all select 2, 2 from dual union all select 3, 2 from dual ) select id, name, nvl(cnt, 0) cnt from developer d left join ( select dev_id, count(1) cnt from project p join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id where p.type = 2 and t.start_date <= trunc(sysdate, 'year') - 1 and (add_months(trunc(sysdate, 'year'), -12) <= t.end_date or t.end_date is null) group by dev_id) t on t.dev_id = d.id
ID NAME CNT
1 Dev1 1
3 Dev3 0
2 Dev2 0
 hidden batch(es)


with developer(id, name) as ( select 1, 'Dev1' from dual union all select 2, 'Dev2' from dual union all select 3, 'Dev3' from dual ), customer (id, name) as (select 1, 'Cust1' from dual), project(id, customer_id, name, type, start_date, end_date) as ( select 1, 1, 'P1', 2, date '2018-04-16', date '2018-06-02' from dual union all select 2, 0, 'P2', 0, date '2017-04-16', date '2017-04-20' from dual union all select 3, 1, 'P3', 0, date '2018-11-20', null from dual ), task(id, name, project_id, type, start_date, end_date) as ( select 1, 'T1', 1, 0, date '2018-06-01', date '2018-06-02' from dual union all select 2, 'T2', 2, 0, date '2017-04-17', date '2017-04-20' from dual union all select 3, 'T3', 3, 0, date '2018-11-21', null from dual ), developer_task(dev_id, task_id) as ( select 1, 1 from dual union all select 2, 2 from dual union all select 3, 2 from dual ) select * from developer where id not in ( select dev_id from project p join task t on t.project_id = p.id join developer_task dt on dt.task_id = t.id where p.type = 0)
ID NAME
1 Dev1
 hidden batch(es)