CREATE OR REPLACE VIEW ACTION_V as
with ACTION as (select 
    FIELD1, FIELD2, TYPE, 
    case
        when PLAN_DATE >= sysdate and STATUS NOT IN ('Завершен','Отменен','Отказан') then 'IN_PROGRESS'
        when PLAN_DATE < sysdate and STATUS NOT IN ('Завершен','Отменен','Отказан') then 'OVERDUE'
        when PLAN_DATE <= sysdate and STATUS IN ('Завершен','Отменен','Отказан') then 'DONE'
    end as STATE
from 
    ACTIONS 
where
    START_DATE > sysdate - 100 and TYPE in ('Type1','Type2'))


select * from(
    select FIELD1, FIELD2, TYPE || '_' || STATE as TYPE_STATE, COUNT(*) as NUMS
    from ACTION
    group by 
        FIELD1, FIELD2, TYPE, STATE 
    order by 
        FIELD1, TYPE, STATE
)
pivot
(
    SUM(NUMS)
    for TYPE_STATE in ('Type1_IN_PROGRESS' as TYPE1_IN_PROGRESS,'Type1_OVERDUE' as TYPE1_OVERDUE,'Type1_DONE' as TYPE1_DONE,
		'Type2_IN_PROGRESS' as TYPE2_IN_PROGRESS,'Type2_OVERDUE' as TYPE2_OVERDUE,'Type2_DONE' as TYPE2_DONE)
)
;


CREATE OR REPLACE VIEW REQUEST_V as
with REQUEST as (select 
    FIELD1, FIELD2, TYPE, 
    case
        when PLAN_DATE >= sysdate and STATUS NOT IN ('Исполнен','Отменен') then 'IN_PROGRESS'
        when PLAN_DATE < sysdate and STATUS NOT IN ('Исполнен','Отменен') then 'OVERDUE'
        when PLAN_DATE <= sysdate and STATUS IN ('Исполнен','Отменен') then 'DONE'
    end as STATE
from 
    REQUESTS 
where
    START_DATE > sysdate - 100 and TYPE in ('Type1','Type2','Type3'))


select * from(
    select FIELD1, FIELD2, TYPE || '_' || STATE as TYPE_STATE, COUNT(*) as NUMS
    from REQUEST
    group by 
        FIELD1, FIELD2, TYPE, STATE 
    order by 
        FIELD1, TYPE, STATE
)
pivot
(
    SUM(NUMS)
    for TYPE_STATE in ('Type1_IN_PROGRESS' as TYPE1_IN_PROGRESS,'Type1_OVERDUE' as TYPE1_OVERDUE,'Type1_DONE' as TYPE1_DONE,
		'Type2_IN_PROGRESS' as TYPE2_IN_PROGRESS,'Type2_OVERDUE' as TYPE2_OVERDUE,'Type2_DONE' as TYPE2_DONE
		'Type3_IN_PROGRESS' as TYPE3_IN_PROGRESS,'Type3_OVERDUE' as TYPE3_OVERDUE,'Type3_DONE' as TYPE3_DONE)
)
;

select tt.*, act.*, req.* from
    (select
        wg.GRNAME, w.FIO, w.FIELD1, wg.FIELD2
    from 
	WORKER_GROUP wg
	join WORKER w ON w.ID=wg.WORKER_ID
	join AREA ar ON ar.ID=wg.AREA_ID
    where 
        ROLE='Manager'
    group by 
		FNAME, FIO, FIELD1, FIELD2) tt
left outer join ACTION_V act ON act.FIELD1=tt.FIELD1 and act.FIELD2=tt.FIELD2
left outer join REQUEST_V req ON req.FIELD1=tt.FIELD1 and req.FIELD2=tt.FIELD2
;