WITH
incidents AS (
    SELECT
      *
    FROM regsysref.sd_incident si
    WHERE si.reg_date >= TO_DATE('2018-07-20', 'yyyy-mm-dd')
    AND si.reg_date < TO_DATE('2018-07-20', 'yyyy-mm-dd') + INTERVAL '1' DAY
    AND si.direction = 'i'
    AND si.reg_department_id = 105
),
all_i AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
),
by_service AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE SERVICE_INT_ID IN (334, 5283, 5200, 5226, 5292, 5392)
),
by_result AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE FEATURE_INT_ID IN (154, 2763, 337)
),
need_fixorder AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID IN (346, 347, 348)
),
need_ss AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID IN (338, 339)
),
for_2lvl AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID = 352
),
question_not_first AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID IN (2235, 5253, 2385, 5387, 5388, 2168, 5104)
),
consultation_given AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID = 5233
),
decided_1lvl AS (
  SELECT
    COUNT(*) AS cnt
  FROM incidents
  WHERE RESOLVED_INT_ID IN (5365, 5358, 5229)
),
all_stat AS (
  SELECT
    'По сервисам' AS text,
    by_service.cnt AS cnt,
    CASE WHEN by_service.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(by_service.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    1 AS position
  FROM all_i, by_service

  UNION

  SELECT
    'Всего  аварии/плановые' AS text,
    by_result.cnt AS cnt,
    CASE WHEN by_result.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(by_result.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    2 AS position
  FROM all_i, by_result

  UNION

  SELECT
    'Требуется ремонт' AS text,
    need_fixorder.cnt AS cnt,
    CASE WHEN need_fixorder.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(need_fixorder.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    3 AS position
  FROM all_i, need_fixorder

  UNION

  SELECT
    'Требуется cc' AS text,
    need_ss.cnt AS cnt,
    CASE WHEN need_ss.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(need_ss.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    4 AS position
  FROM all_i, need_ss

  UNION

  SELECT
    'На второй' AS text,
    for_2lvl.cnt AS cnt,
    CASE WHEN for_2lvl.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(for_2lvl.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    5 AS position
  FROM all_i, for_2lvl

  UNION

  SELECT
    'Вопрос не с первого раза' AS text,
    question_not_first.cnt AS cnt,
    CASE WHEN question_not_first.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(question_not_first.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    6 AS position
  FROM all_i, question_not_first

  UNION

  SELECT
    'Дана консультация' AS text,
    consultation_given.cnt AS cnt,
    CASE WHEN consultation_given.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(consultation_given.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    7 AS position
  FROM all_i, consultation_given

  UNION

  SELECT
    'Решили на первом уровне' AS text,
    decided_1lvl.cnt AS cnt,
    CASE WHEN decided_1lvl.cnt > 0 AND all_i.cnt > 0
      THEN ROUND(decided_1lvl.cnt / all_i.cnt * 100, 2)
      ELSE 0
    END AS pct,
    7 AS position
  FROM all_i, decided_1lvl
)

SELECT text AS TEXT, cnt AS CNT, pct AS PCT FROM all_stat ORDER BY position