Тут сплошной внутряк
Чем занимаются работающие Job’ы
SELECT PSO_INPUT.PKG_CTX_CONTROL.GETJOBSTATUS (UPPER (a.job_name)) status,
a.OWNER,
a.job_name,
a.state,
A.LAST_START_DATE,
TO_CHAR (EXTRACT (DAY FROM (SYSDATE - A.LAST_START_DATE)),
'fm99999')
|| ':'
|| TO_CHAR (EXTRACT (HOUR FROM (SYSDATE - A.LAST_START_DATE)), 'fm00')
|| ':'
|| TO_CHAR (EXTRACT (MINUTE FROM (SYSDATE - A.LAST_START_DATE)),
'fm00')
|| ':'
|| TO_CHAR (EXTRACT (SECOND FROM (SYSDATE - A.LAST_START_DATE)))
AS duration
FROM dba_scheduler_jobs a
WHERE a.owner IN ('PSO_INPUT', 'PNH_FORMS')
AND a.job_name LIKE '%CALC%'
AND a.state = 'RUNNING'
запросы областей
SELECT reports_all AS "отчетов всего",
reports_ARM AS "отчетов от АРМ",
reports_web AS "отчетов от web-портала",
respondents_all AS "респондентов всего",
respondents_arm AS "респондентов от АРМ",
respondents_web AS "респондентов от web-портала",
frm_all AS "по формам ГСН всего ",
frm_arm AS "по формам ГСН от АРМ",
frm_web AS "по формам ГСН от web-портала",
CASE
WHEN region IS NULL THEN 'Всего по РБ'
WHEN region = 1 THEN 'Брестская область'
WHEN region = 2 THEN 'Витебская область'
WHEN region = 3 THEN 'Гомельская область'
WHEN region = 4 THEN 'Гродненская область'
WHEN region = 5 THEN 'г.Минск'
WHEN region = 6 THEN 'Минская область'
WHEN region = 7 THEN 'Могилевская область'
END
AS "Область"
FROM ( SELECT COUNT (id_doc) AS reports_all,
SUM (CASE WHEN id_ref = 182 THEN 1 ELSE 0 END) AS reports_ARM,
SUM (CASE WHEN id_ref = -182 THEN 1 ELSE 0 END) AS reports_web,
COUNT (DISTINCT code_esn) AS respondents_all,
COUNT (
DISTINCT CASE WHEN id_ref = 182 THEN code_esn ELSE NULL END)
AS respondents_arm,
COUNT (
DISTINCT CASE
WHEN id_ref = -182 THEN code_esn
ELSE NULL
END)
AS respondents_web,
COUNT (DISTINCT index_form) AS frm_all,
COUNT (
DISTINCT CASE
WHEN id_ref = 182 THEN index_form
ELSE NULL
END)
AS frm_arm,
COUNT (
DISTINCT CASE
WHEN id_ref = -182 THEN index_form
ELSE NULL
END)
AS frm_web,
region
FROM (SELECT id_doc,
code_esn,
SUBSTR (code_esn, 9, 1) AS region,
index_form,
id_ref,
CASE WHEN id_ref = 182 THEN 1 ELSE 0 END AS arm,
CASE WHEN id_ref = -182 THEN 1 ELSE 0 END AS web
FROM PSO_SOO.INCOMEDOCLIST i1
WHERE I1.RECEIVE_DATE >
TO_DATE ('01.01.2018', 'dd.mm.yyyy')
AND I1.RECEIVE_DATE <
TO_DATE ('01.02.2018', 'dd.mm.yyyy')
AND I1.PROCESSING_RESULT = 0) t1
GROUP BY ROLLUP (region)
ORDER BY region NULLS FIRST)
Вторая версия
WITH t_all -- получение информации в целом по республике с разбивкой по месяцам и источникам
AS ( SELECT COUNT (ID_doc) reports,
COUNT (DISTINCT code_esn) respondents,
COUNT (DISTINCT index_form) forms,
EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)) AS mon,
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)) AS year,
NVL (id_ref, 0) AS id_ref
FROM pso_soo.incomedoclist
WHERE PSO_SOO.INCOMEDOCLIST.RECEIVE_DATE >
TO_DATE ('01.04.2017', 'dd.mm.yyyy')
AND PSO_SOO.INCOMEDOCLIST.PROCESSING_RESULT = 0
GROUP BY GROUPING SETS (
EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)),
id_ref),
(EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)))),
t_region -- получение информации в целом по области с разбивкой по месяцам и источникам
AS ( SELECT COUNT (ID_doc) reports,
COUNT (DISTINCT code_esn) respondents,
COUNT (DISTINCT index_form) forms,
EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)) AS mon,
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)) AS year,
NVL (id_ref, 0) AS id_ref
FROM pso_soo.incomedoclist
WHERE PSO_SOO.INCOMEDOCLIST.RECEIVE_DATE >
TO_DATE ('01.04.2017', 'dd.mm.yyyy')
AND SUBSTR (code_esn, 9, 1) = :region
AND PSO_SOO.INCOMEDOCLIST.PROCESSING_RESULT = 0
GROUP BY GROUPING SETS (
EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)),
id_ref),
(EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),
EXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)))),
t_res -- сливаем данные республики и области
AS (SELECT t_all.id_ref,
t_all.year,
t_all.mon,
t_all.reports all_reports,
t_all.respondents all_respondents,
t_all.forms all_forms,
t_region.reports reg_reports,
t_region.respondents reg_respondents,
t_region.forms reg_forms
FROM t_all, t_region
WHERE t_all.year = t_region.year
AND t_all.mon = t_region.mon
AND t_all.id_ref = t_region.id_ref)
---Начался изврат с разбивкой по красоте
SELECT year,
mon,
' Отчетов' AS title,
SUM (CASE WHEN id_ref = -182 THEN reg_reports ELSE 0 END)
AS reg_portal,
SUM (CASE WHEN id_ref = 182 THEN reg_reports ELSE 0 END) AS reg_arm,
SUM (CASE WHEN id_ref = 0 THEN reg_reports ELSE 0 END) AS reg_all,
SUM (CASE WHEN id_ref = -182 THEN all_reports ELSE 0 END)
AS full_portal,
SUM (CASE WHEN id_ref = 182 THEN all_reports ELSE 0 END) AS full_arm,
SUM (CASE WHEN id_ref = 0 THEN all_reports ELSE 0 END) AS full_all
FROM t_res
GROUP BY year, mon
UNION ALL
SELECT year,
mon,
' Респондентов' AS title,
SUM (CASE WHEN id_ref = -182 THEN reg_respondents ELSE 0 END)
AS reg_portal,
SUM (CASE WHEN id_ref = 182 THEN reg_respondents ELSE 0 END)
AS reg_arm,
SUM (CASE WHEN id_ref = 0 THEN reg_respondents ELSE 0 END) AS reg_all,
SUM (CASE WHEN id_ref = -182 THEN all_respondents ELSE 0 END)
AS full_portal,
SUM (CASE WHEN id_ref = 182 THEN all_respondents ELSE 0 END)
AS full_arm,
SUM (CASE WHEN id_ref = 0 THEN all_respondents ELSE 0 END) AS full_all
FROM t_res
GROUP BY year, mon
UNION ALL
SELECT year,
mon,
' Форм статотчетности' AS title,
SUM (CASE WHEN id_ref = -182 THEN reg_forms ELSE 0 END) AS reg_portal,
SUM (CASE WHEN id_ref = 182 THEN reg_forms ELSE 0 END) AS reg_arm,
SUM (CASE WHEN id_ref = 0 THEN reg_forms ELSE 0 END) AS reg_all,
SUM (CASE WHEN id_ref = -182 THEN all_forms ELSE 0 END) AS full_portal,
SUM (CASE WHEN id_ref = 182 THEN all_forms ELSE 0 END) AS full_arm,
SUM (CASE WHEN id_ref = 0 THEN all_forms ELSE 0 END) AS full_all
FROM t_res
GROUP BY year, mon
ORDER BY 1, 2, 3