{"id":662,"date":"2017-10-05T09:49:34","date_gmt":"2017-10-05T05:49:34","guid":{"rendered":"https:\/\/provolotski.wordpress.com\/?p=662"},"modified":"2017-10-05T09:49:34","modified_gmt":"2017-10-05T05:49:34","slug":"%d0%bf%d0%be%d0%bb%d0%b5%d0%b7%d0%bd%d1%8b%d0%b5-sql-%d0%b4%d0%bb%d1%8f-eisgs","status":"publish","type":"post","link":"https:\/\/provolotski.by\/?p=662","title":{"rendered":"\u041f\u043e\u043b\u0435\u0437\u043d\u044b\u0435 sql \u0434\u043b\u044f EISGS"},"content":{"rendered":"<p>\u0422\u0443\u0442 \u0441\u043f\u043b\u043e\u0448\u043d\u043e\u0439 \u0432\u043d\u0443\u0442\u0440\u044f\u043a<\/p>\n<p><!--more--><\/p>\n<p>\u0427\u0435\u043c \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0442\u0441\u044f \u0440\u0430\u0431\u043e\u0442\u0430\u044e\u0449\u0438\u0435 Job&#8217;\u044b<\/p>\n<pre>SELECT PSO_INPUT.PKG_CTX_CONTROL.GETJOBSTATUS (UPPER (a.job_name)) status,\na.OWNER,\na.job_name,\na.state,\nA.LAST_START_DATE,\nTO_CHAR (EXTRACT (DAY FROM (SYSDATE - A.LAST_START_DATE)),\n'fm99999')\n|| ':'\n|| TO_CHAR (EXTRACT (HOUR FROM (SYSDATE - A.LAST_START_DATE)), 'fm00')\n|| ':'\n|| TO_CHAR (EXTRACT (MINUTE FROM (SYSDATE - A.LAST_START_DATE)),\n'fm00')\n|| ':'\n|| TO_CHAR (EXTRACT (SECOND FROM (SYSDATE - A.LAST_START_DATE)))\nAS duration\nFROM dba_scheduler_jobs a\nWHERE a.owner IN ('PSO_INPUT', 'PNH_FORMS')\nAND a.job_name LIKE '%CALC%'\nAND a.state = 'RUNNING'\n<\/pre>\n<p>\u0437\u0430\u043f\u0440\u043e\u0441\u044b \u043e\u0431\u043b\u0430\u0441\u0442\u0435\u0439<\/p>\n<pre>SELECT reports_all AS \"\u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0432\u0441\u0435\u0433\u043e\",\nreports_ARM AS \"\u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u043e\u0442 \u0410\u0420\u041c\",\nreports_web AS \"\u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u043e\u0442 web-\u043f\u043e\u0440\u0442\u0430\u043b\u0430\",\nrespondents_all AS \"\u0440\u0435\u0441\u043f\u043e\u043d\u0434\u0435\u043d\u0442\u043e\u0432 \u0432\u0441\u0435\u0433\u043e\",\nrespondents_arm AS \"\u0440\u0435\u0441\u043f\u043e\u043d\u0434\u0435\u043d\u0442\u043e\u0432 \u043e\u0442 \u0410\u0420\u041c\",\nrespondents_web AS \"\u0440\u0435\u0441\u043f\u043e\u043d\u0434\u0435\u043d\u0442\u043e\u0432 \u043e\u0442 web-\u043f\u043e\u0440\u0442\u0430\u043b\u0430\",\nfrm_all AS \"\u043f\u043e \u0444\u043e\u0440\u043c\u0430\u043c \u0413\u0421\u041d \u0432\u0441\u0435\u0433\u043e \",\nfrm_arm AS \"\u043f\u043e \u0444\u043e\u0440\u043c\u0430\u043c \u0413\u0421\u041d \u043e\u0442 \u0410\u0420\u041c\",\nfrm_web AS \"\u043f\u043e \u0444\u043e\u0440\u043c\u0430\u043c \u0413\u0421\u041d \u043e\u0442 web-\u043f\u043e\u0440\u0442\u0430\u043b\u0430\",\nCASE\nWHEN region IS NULL THEN '\u0412\u0441\u0435\u0433\u043e \u043f\u043e \u0420\u0411'\nWHEN region = 1 THEN '\u0411\u0440\u0435\u0441\u0442\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nWHEN region = 2 THEN '\u0412\u0438\u0442\u0435\u0431\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nWHEN region = 3 THEN '\u0413\u043e\u043c\u0435\u043b\u044c\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nWHEN region = 4 THEN '\u0413\u0440\u043e\u0434\u043d\u0435\u043d\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nWHEN region = 5 THEN '\u0433.\u041c\u0438\u043d\u0441\u043a'\nWHEN region = 6 THEN '\u041c\u0438\u043d\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nWHEN region = 7 THEN '\u041c\u043e\u0433\u0438\u043b\u0435\u0432\u0441\u043a\u0430\u044f \u043e\u0431\u043b\u0430\u0441\u0442\u044c'\nEND\nAS \"\u041e\u0431\u043b\u0430\u0441\u0442\u044c\"\nFROM (  SELECT COUNT (id_doc) AS reports_all,\nSUM (CASE WHEN id_ref = 182 THEN 1 ELSE 0 END) AS reports_ARM,\nSUM (CASE WHEN id_ref = -182 THEN 1 ELSE 0 END) AS reports_web,\nCOUNT (DISTINCT code_esn) AS respondents_all,\nCOUNT (\nDISTINCT CASE WHEN id_ref = 182 THEN code_esn ELSE NULL END)\nAS respondents_arm,\nCOUNT (\nDISTINCT CASE\nWHEN id_ref = -182 THEN code_esn\nELSE NULL\nEND)\nAS respondents_web,\nCOUNT (DISTINCT index_form) AS frm_all,\nCOUNT (\nDISTINCT CASE\nWHEN id_ref = 182 THEN index_form\nELSE NULL\nEND)\nAS frm_arm,\nCOUNT (\nDISTINCT CASE\nWHEN id_ref = -182 THEN index_form\nELSE NULL\nEND)\nAS frm_web,\nregion\nFROM (SELECT id_doc,\ncode_esn,\nSUBSTR (code_esn, 9, 1) AS region,\nindex_form,\nid_ref,\nCASE WHEN id_ref = 182 THEN 1 ELSE 0 END AS arm,\nCASE WHEN id_ref = -182 THEN 1 ELSE 0 END AS web\nFROM PSO_SOO.INCOMEDOCLIST i1\nWHERE     I1.RECEIVE_DATE &gt;\nTO_DATE ('01.01.2018', 'dd.mm.yyyy')\nAND I1.RECEIVE_DATE &lt;\nTO_DATE (&#039;01.02.2018&#039;, &#039;dd.mm.yyyy&#039;)\nAND I1.PROCESSING_RESULT = 0) t1\nGROUP BY ROLLUP (region)\nORDER BY region NULLS FIRST)\n<\/pre>\n<p>\u0412\u0442\u043e\u0440\u0430\u044f \u0432\u0435\u0440\u0441\u0438\u044f<\/p>\n<pre>\nWITH t_all -- \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u0435 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u0432 \u0446\u0435\u043b\u043e\u043c \u043f\u043e \u0440\u0435\u0441\u043f\u0443\u0431\u043b\u0438\u043a\u0435 \u0441 \u0440\u0430\u0437\u0431\u0438\u0432\u043a\u043e\u0439 \u043f\u043e \u043c\u0435\u0441\u044f\u0446\u0430\u043c \u0438 \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u0430\u043c\nAS ( SELECT COUNT (ID_doc) reports,\nCOUNT (DISTINCT code_esn) respondents,\nCOUNT (DISTINCT index_form) forms,\nEXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)) AS mon,\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)) AS year,\nNVL (id_ref, 0) AS id_ref\nFROM pso_soo.incomedoclist\nWHERE PSO_SOO.INCOMEDOCLIST.RECEIVE_DATE &gt;\nTO_DATE ('01.04.2017', 'dd.mm.yyyy')\nAND PSO_SOO.INCOMEDOCLIST.PROCESSING_RESULT = 0\nGROUP BY GROUPING SETS (\nEXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)),\nid_ref),\n(EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)))),\nt_region -- \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u0435 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u0432 \u0446\u0435\u043b\u043e\u043c \u043f\u043e \u043e\u0431\u043b\u0430\u0441\u0442\u0438 \u0441 \u0440\u0430\u0437\u0431\u0438\u0432\u043a\u043e\u0439 \u043f\u043e \u043c\u0435\u0441\u044f\u0446\u0430\u043c \u0438 \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u0430\u043c\nAS ( SELECT COUNT (ID_doc) reports,\nCOUNT (DISTINCT code_esn) respondents,\nCOUNT (DISTINCT index_form) forms,\nEXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)) AS mon,\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)) AS year,\nNVL (id_ref, 0) AS id_ref\nFROM pso_soo.incomedoclist\nWHERE PSO_SOO.INCOMEDOCLIST.RECEIVE_DATE &gt;\nTO_DATE ('01.04.2017', 'dd.mm.yyyy')\nAND SUBSTR (code_esn, 9, 1) = :region\nAND PSO_SOO.INCOMEDOCLIST.PROCESSING_RESULT = 0\nGROUP BY GROUPING SETS (\nEXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)),\nid_ref),\n(EXTRACT (MONTH FROM CAST (RECEIVE_date AS TIMESTAMP)),\nEXTRACT (YEAR FROM CAST (RECEIVE_date AS TIMESTAMP)))),\nt_res -- \u0441\u043b\u0438\u0432\u0430\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0435 \u0440\u0435\u0441\u043f\u0443\u0431\u043b\u0438\u043a\u0438 \u0438 \u043e\u0431\u043b\u0430\u0441\u0442\u0438\nAS (SELECT t_all.id_ref,\nt_all.year,\nt_all.mon,\nt_all.reports all_reports,\nt_all.respondents all_respondents,\nt_all.forms all_forms,\nt_region.reports reg_reports,\nt_region.respondents reg_respondents,\nt_region.forms reg_forms\nFROM t_all, t_region\nWHERE t_all.year = t_region.year\nAND t_all.mon = t_region.mon\nAND t_all.id_ref = t_region.id_ref)\n---\u041d\u0430\u0447\u0430\u043b\u0441\u044f \u0438\u0437\u0432\u0440\u0430\u0442 \u0441 \u0440\u0430\u0437\u0431\u0438\u0432\u043a\u043e\u0439 \u043f\u043e \u043a\u0440\u0430\u0441\u043e\u0442\u0435\nSELECT year,\nmon,\n' \u041e\u0442\u0447\u0435\u0442\u043e\u0432' AS title,\nSUM (CASE WHEN id_ref = -182 THEN reg_reports ELSE 0 END)\nAS reg_portal,\nSUM (CASE WHEN id_ref = 182 THEN reg_reports ELSE 0 END) AS reg_arm,\nSUM (CASE WHEN id_ref = 0 THEN reg_reports ELSE 0 END) AS reg_all,\nSUM (CASE WHEN id_ref = -182 THEN all_reports ELSE 0 END)\nAS full_portal,\nSUM (CASE WHEN id_ref = 182 THEN all_reports ELSE 0 END) AS full_arm,\nSUM (CASE WHEN id_ref = 0 THEN all_reports ELSE 0 END) AS full_all\nFROM t_res\nGROUP BY year, mon\nUNION ALL\nSELECT year,\nmon,\n' \u0420\u0435\u0441\u043f\u043e\u043d\u0434\u0435\u043d\u0442\u043e\u0432' AS title,\nSUM (CASE WHEN id_ref = -182 THEN reg_respondents ELSE 0 END)\nAS reg_portal,\nSUM (CASE WHEN id_ref = 182 THEN reg_respondents ELSE 0 END)\nAS reg_arm,\nSUM (CASE WHEN id_ref = 0 THEN reg_respondents ELSE 0 END) AS reg_all,\nSUM (CASE WHEN id_ref = -182 THEN all_respondents ELSE 0 END)\nAS full_portal,\nSUM (CASE WHEN id_ref = 182 THEN all_respondents ELSE 0 END)\nAS full_arm,\nSUM (CASE WHEN id_ref = 0 THEN all_respondents ELSE 0 END) AS full_all\nFROM t_res\nGROUP BY year, mon\nUNION ALL\nSELECT year,\nmon,\n' \u0424\u043e\u0440\u043c \u0441\u0442\u0430\u0442\u043e\u0442\u0447\u0435\u0442\u043d\u043e\u0441\u0442\u0438' AS title,\nSUM (CASE WHEN id_ref = -182 THEN reg_forms ELSE 0 END) AS reg_portal,\nSUM (CASE WHEN id_ref = 182 THEN reg_forms ELSE 0 END) AS reg_arm,\nSUM (CASE WHEN id_ref = 0 THEN reg_forms ELSE 0 END) AS reg_all,\nSUM (CASE WHEN id_ref = -182 THEN all_forms ELSE 0 END) AS full_portal,\nSUM (CASE WHEN id_ref = 182 THEN all_forms ELSE 0 END) AS full_arm,\nSUM (CASE WHEN id_ref = 0 THEN all_forms ELSE 0 END) AS full_all\nFROM t_res\nGROUP BY year, mon\nORDER BY 1, 2, 3\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u0422\u0443\u0442 \u0441\u043f\u043b\u043e\u0448\u043d\u043e\u0439 \u0432\u043d\u0443\u0442\u0440\u044f\u043a<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-662","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/provolotski.by\/index.php?rest_route=\/wp\/v2\/posts\/662","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/provolotski.by\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/provolotski.by\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/provolotski.by\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/provolotski.by\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=662"}],"version-history":[{"count":0,"href":"https:\/\/provolotski.by\/index.php?rest_route=\/wp\/v2\/posts\/662\/revisions"}],"wp:attachment":[{"href":"https:\/\/provolotski.by\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=662"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/provolotski.by\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=662"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/provolotski.by\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=662"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}