54 lines
2.5 KiB
SQL
54 lines
2.5 KiB
SQL
INSERT INTO dwh.MNP_DW_REPORT_CAPACITAEVASIONE
|
|
(MESE, GIORNI_LAVORATIVI, RICHIESTE_PRESEINCARICO, RICHIESTE_SCARTATE_WAITLIST, RICHIESTE_SCARTATE_MANPROGR)
|
|
VALUES(
|
|
TO_CHAR(Last_Day(ADD_MONTHS(sysdate,-2))+1,'MM/YYYY'),
|
|
dwh.FUN_GIORNI_LAVORATIVI(Last_Day(ADD_MONTHS(sysdate,-1)), Last_Day(ADD_MONTHS(sysdate,-2))+1),
|
|
( select count(*)
|
|
from mnp_storico_richiesta t, mnp_gestione_richiesta r
|
|
where t.id_richiesta = r.id_richiesta
|
|
and (r.richiestaadhocaom = 0 and r.richiestaadhoc = 0)
|
|
and t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and r.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato_a = 3),
|
|
( select count(*)
|
|
from mnp_gestione_richiesta t
|
|
where t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato = 4
|
|
and (t.richiestaadhocaom = 0 and t.richiestaadhoc = 0)
|
|
and t.codice_controllo_validazione = 7),
|
|
( select count(*)
|
|
from mnp_gestione_richiesta t
|
|
where t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato = 4
|
|
and t.codice_controllo_validazione = 3)
|
|
);
|
|
|
|
INSERT INTO dwh.MNP_DW_REPORT_CAPACITAEVASIONE
|
|
(MESE, GIORNI_LAVORATIVI, RICHIESTE_PRESEINCARICO, RICHIESTE_SCARTATE_WAITLIST, RICHIESTE_SCARTATE_MANPROGR)
|
|
VALUES(
|
|
TO_CHAR(Last_Day(ADD_MONTHS(sysdate,-3))+1,'MM/YYYY'),
|
|
dwh.FUN_GIORNI_LAVORATIVI(Last_Day(ADD_MONTHS(sysdate,-1)), Last_Day(ADD_MONTHS(sysdate,-2))+1),
|
|
( select count(*)
|
|
from mnp_storico_richiesta t, mnp_gestione_richiesta r
|
|
where t.id_richiesta = r.id_richiesta
|
|
and (r.richiestaadhocaom = 0 and r.richiestaadhoc = 0)
|
|
and t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and r.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato_a = 3),
|
|
( select count(*)
|
|
from mnp_gestione_richiesta t
|
|
where t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato = 4
|
|
and (t.richiestaadhocaom = 0 and t.richiestaadhoc = 0)
|
|
and t.codice_controllo_validazione = 7),
|
|
( select count(*)
|
|
from mnp_gestione_richiesta t
|
|
where t.dataricezionerichiesta between Last_Day(ADD_MONTHS(sysdate,-2))+1 AND Last_Day(ADD_MONTHS(sysdate,-1))
|
|
and t.stato = 4
|
|
and t.codice_controllo_validazione = 3)
|
|
);
|
|
|
|
|
|
|
|
commit;
|