WITH cte_osn AS (
    SELECT
        gt.name,
        gt.id,
        gt.parent_geo_tag_id,
        gt.level,
        CASE WHEN gt.level = 1 THEN gt.id when gt.level = 2 THEN gt.parent_geo_tag_id END AS rn_parent_geo_tag_id,
        nullif(count(*) FILTER (WHERE cat.code = 'dmp'), 0) AS w1,
        nullif(count(*) FILTER (WHERE cat.code = 'oth'), 0) AS w2,
        nullif(count(*) FILTER (WHERE cat.code = 'orc'), 0) AS w3,
        nullif(count(*) FILTER (WHERE cat.code = 'lc'), 0)  AS w4,
        nullif(count(*) FILTER (WHERE cat.code = 'qmp'), 0) AS w5,
        nullif(count(*) FILTER (WHERE cat.code = 'uir'), 0) AS w6,
        nullif(count(*) FILTER (WHERE cat.code = 'mo'), 0)  AS w7,
        nullif(count(*) FILTER (WHERE cat.code = 'wwp'), 0) AS w8,
        nullif(count(*) FILTER (WHERE cat.code = 'tko'), 0) AS w9,
        nullif(count(*) FILTER (WHERE cat.code = 'smo'), 0) AS w10,
        nullif(count(*) FILTER (WHERE cat.code = 'qmo'), 0) AS w11,
        nullif(count(*) FILTER (WHERE cat.code = 'vmo'), 0) AS w12,
        nullif(count(*) FILTER (WHERE cat.code = 'col'), 0) AS w13,
        count(*) AS w0
    FROM geo_tags gt
    LEFT JOIN (
         SELECT h.* FROM handlings h
         JOIN statuses s ON s.id = h.status_id
         WHERE s.code <> 'handling.rejected'
            AND h.created_date BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
    ) h ON gt.id = h.region_id
    LEFT JOIN refs cat ON cat.id = h.category_id
    WHERE gt.level IN (2,1) --and created_date is not null
        AND (gt.parent_geo_tag_id = ANY (ARRAY[:geoTagIds] :: INT[]) OR gt.id = ANY (ARRAY[:geoTagIds] :: INT[])) -- округ или список субъектов
    GROUP BY gt.name, gt.level, gt.id, gt.parent_geo_tag_id
), cte_treated AS (
    SELECT
        gt2.geo_tag_id          AS id,
        count(gt2.geo_tag_name) AS treated --Рассмотрено
    FROM (
        SELECT
            h.id,
            h.status_id,
            h.surname,
            h.name,
            g.name              AS geo_tag_name,
            g.level             AS geo_tag_level,
            g.parent_geo_tag_id AS parent_geo_tag_id,
            g.id                AS geo_tag_id
        FROM handlings h
        JOIN handlings_status_changes_relations scr ON h.id = scr.handling_id
        JOIN status_changes sc ON scr.statuschange_id = sc.id AND sc.is_successfully
        LEFT JOIN geo_tags g ON h.region_id = g.id
        JOIN statuses s ON s.id = h.status_id
        JOIN statuses s1 ON s1.id = sc.to_status_id
        WHERE s.code = 'handling.answered'
            AND h.created_date BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
            AND h.id IN (
                SELECT
                    h.id
                FROM handlings h
                JOIN handling_replies hr ON hr.handling_id = h.id
                GROUP BY h.id
                HAVING max(hr.created) BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
            )
        GROUP BY h.id, g.id
        HAVING NOT bool_or(s1.code = 'handling.forwarded')
    ) gt2
    GROUP BY gt2.geo_tag_level, gt2.parent_geo_tag_id, gt2.geo_tag_id
), cte_days_for_reply AS (
    SELECT
        gt2.id,
        round(
            avg(nullif(to_char(hr.created - h.created_date, 'DD'), '') :: int), 0
        ) AS days_for_reply --Время рассмотрения
    FROM handlings h
    JOIN (
        SELECT hr.created, hr.handling_id
        FROM handling_replies hr
        INNER JOIN (
            SELECT
                id, max(created) OVER (PARTITION BY handling_id) = created AS is_last_created
            FROM handling_replies
        ) t ON hr.id = t.id
       WHERE t.is_last_created
    ) hr ON h.id = hr.handling_id
    JOIN geo_tags gt2 ON gt2.id = h.region_id
    JOIN statuses s ON s.id = h.status_id
    JOIN refs cat ON cat.id = h.category_id
    WHERE s.code IN ('handling.answered', 'handling.forwarded')
        AND hr.created BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
        AND cat.code <> 'uir'
        AND nullif(to_char(hr.created - h.created_date, 'DD'), '') :: int < 90
    GROUP BY gt2.id, gt2.level, gt2.parent_geo_tag_id
), cte_sc AS (
        SELECT
            sc.id, sc.to_status_id, sc.created_at, sc.is_successfully
        FROM status_changes sc
        INNER JOIN (
            SELECT
                id, max(created_at) OVER (PARTITION BY hscr.handling_id, sc.to_status_id) = sc.created_at AS is_last_created
            FROM status_changes sc
            INNER JOIN handlings_status_changes_relations hscr ON sc.id = hscr.statuschange_id
        ) t ON t.id = sc.id
        WHERE t.is_last_created
), cte_oiv AS (
    SELECT
        gt2.id, count(DISTINCT h.id) AS oiv --Перенаправлено в ОИВ
    FROM handlings h
    JOIN handlings_status_changes_relations hscr ON hscr.handling_id = h.id
    JOIN (
        SELECT
            sc.id, sc.to_status_id, sc.created_at, sc.is_successfully
        FROM status_changes sc
        INNER JOIN (
            SELECT
                id, max(created_at) OVER (PARTITION BY hscr.handling_id, sc.to_status_id) = sc.created_at AS is_last_created
            FROM status_changes sc
            INNER JOIN handlings_status_changes_relations hscr ON sc.id = hscr.statuschange_id
        ) t ON t.id = sc.id
        WHERE t.is_last_created
    ) sc ON hscr.statuschange_id = sc.id AND sc.is_successfully = 'true'
    JOIN statuses s ON s.id = h.status_id
    JOIN statuses s1 ON s1.id = sc.to_status_id
    LEFT JOIN geo_tags gt2 ON h.region_id = gt2.id
    WHERE h.created_date BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
        AND sc.created_at BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
        AND s1.code = 'handling.forwarded'
        AND h.id IN (sc.id)
    GROUP BY gt2.id, gt2.name
), cte_control AS (
    SELECT
        gt2.id,
        count(h.*) AS control --На контроле
    FROM handlings h
    LEFT JOIN geo_tags gt2 ON h.region_id = gt2.id
    JOIN statuses s ON s.id = h.status_id
    WHERE s.code = 'handling.forwarded'
        AND h.created_date BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
    GROUP BY gt2.id
), cte_control_50 AS (
    SELECT
        gt2.id,
        count(h.*) AS control_50 --На контроле 50
    FROM handlings h
    LEFT JOIN geo_tags gt2 ON h.region_id = gt2.id
    JOIN statuses s ON s.id = h.status_id
    WHERE s.code = 'handling.forwarded'
        AND h.created_date BETWEEN coalesce(:dateFrom :: date, '-infinity') AND coalesce(:dateTo :: date, 'infinity')
        AND (now() :: date - created_date :: date > 50)
    GROUP BY gt2.id
), cte_it_sum AS (
    SELECT
        rank() OVER (ORDER BY w.rn_parent_geo_tag_id, w.id) AS rn,
        w.id,
        w.name,
        coalesce(sum(w.w0),  0) + coalesce(sum(w.w1),  0) + coalesce(sum(w.w2),  0) +
        coalesce(sum(w.w3),  0) + coalesce(sum(w.w4),  0) + coalesce(sum(w.w5),  0) +
        coalesce(sum(w.w6),  0) + coalesce(sum(w.w7),  0) + coalesce(sum(w.w8),  0) +
        coalesce(sum(w.w9),  0) + coalesce(sum(w.w10), 0) + coalesce(sum(w.w11), 0) +
        coalesce(sum(w.w12), 0) + coalesce(sum(w.w13), 0) AS summa
    FROM cte_osn w
    GROUP BY  w.rn_parent_geo_tag_id, w.id, w.name
), cte_sf_data AS (
    SELECT
        rank() OVER (ORDER BY it.rn)                                        AS rn,
        1                                                                   AS total_group,
        pgt.id                                                              AS fo_group,
        gt.name                                                             AS sf_name,
        pgt.name                                                            AS fo_name,
        nullif(it.summa, 0)                                                 AS summary, --2
        nullif(w.w0, 0)                                                     AS in_ais,  --3
        w.w0 * 100 / nullif(it.summa, 0)                                    AS p4,      --4
        w.w1,                                                                           --5
        w.w1 * 100 / nullif(it.summa, 0)                                    AS p6,      --6
        w.w2,                                                                           --7
        w.w2 * 100 / nullif(it.summa, 0)                                    AS p8,      --8
        w.w3,                                                                           --9
        w.w3 * 100 / nullif(it.summa, 0)                                    AS p10,     --10
        w.w4,                                                                           --11
        w.w4 * 100 / nullif(it.summa, 0)                                    AS p12,     --12
        w.w5,                                                                           --13
        w.w6,                                                                           --15
        w.w7,                                                                           --17
        w.w8,                                                                           --19
        w.w9,                                                                           --21
        w.w10,                                                                          --23
        w.w11,                                                                          --25
        w.w12,                                                                          --27
        w.w13,                                                                          --29
        t.treated,                                                                      --31
        dfr.days_for_reply,                                                             --32
        o.oiv,                                                                          --33
        control,                                                                        --34
        control_50                                                                      --35
    FROM cte_osn w
    JOIN geo_tags gt ON gt.id = w.id
    JOIN geo_tags pgt ON gt.lft BETWEEN pgt.lft AND pgt.rgt AND pgt.level = 1
    JOIN cte_it_sum it ON it.id = w.id
    LEFT JOIN cte_treated t ON t.id = w.id
    LEFT JOIN cte_days_for_reply dfr ON dfr.id = t.id
    LEFT JOIN cte_oiv o ON o.id = dfr.id
    LEFT JOIN cte_control c ON  c.id = o.id
    LEFT JOIN cte_control_50 c50 ON c50.id = c.id
    WHERE w.level = 2 OR (
        coalesce(w1, 0) + coalesce(w2, 0) + coalesce(w3, 0) + coalesce(w4, 0) +
        coalesce(w5, 0) + coalesce(w6, 0) + coalesce(w7, 0) + coalesce(w8, 0) +
        coalesce(w9, 0) + coalesce(w10, 0) + coalesce(w11, 0) + coalesce(w12, 0) + coalesce(w13, 0) > 0
    )
), cte_total_by_rf AS (
    SELECT
        sum(in_ais) * 100.0 / nullif(sum(summary), 0) AS p4_rf,
        sum(w1)  * 100.0 / nullif(sum(summary), 0) AS p6_rf,
        sum(w2)  * 100.0 / nullif(sum(summary), 0) AS p8_rf,
        sum(w3)  * 100.0 / nullif(sum(summary), 0) AS p10_rf,
        sum(w4)  * 100.0 / nullif(sum(summary), 0) AS p12_rf,
        sum(w5)  * 100.0 / nullif(sum(summary), 0) AS p14_rf,
        sum(w6)  * 100.0 / nullif(sum(summary), 0) AS p16_rf,
        sum(w7)  * 100.0 / nullif(sum(summary), 0) AS p18_rf,
        sum(w8)  * 100.0 / nullif(sum(summary), 0) AS p20_rf,
        sum(w9)  * 100.0 / nullif(sum(summary), 0) AS p22_rf,
        sum(w10) * 100.0 / nullif(sum(summary), 0) AS p24_rf,
        sum(w11) * 100.0 / nullif(sum(summary), 0) AS p26_rf,
        sum(w12) * 100.0 / nullif(sum(summary), 0) AS p28_rf,
        sum(w13) * 100.0 / nullif(sum(summary), 0) AS p30_rf
    FROM cte_sf_data
), cte_total_by_fo AS (
    SELECT
        csd.fo_group,
        sum(in_ais) * 100.0 / nullif(sum(csd.summary), 0)   AS p4_fo,
        sum(w1) * 100.0 / nullif(sum(csd.summary), 0)       AS p6_fo,
        sum(w2) * 100.0 / nullif(sum(csd.summary), 0)       AS p8_fo,
        sum(w3) * 100.0 / nullif(sum(csd.summary), 0)       AS p10_fo,
        sum(w4) * 100.0 / nullif(sum(csd.summary), 0)       AS p12_fo,

        sum(w5) * 100.0 / nullif(sum(csd.summary), 0)       AS p14_fo,
        sum(w6) * 100.0 / nullif(sum(csd.summary), 0)       AS p16_fo,
        sum(w7) * 100.0 / nullif(sum(csd.summary), 0)       AS p18_fo,
        sum(w8) * 100.0 / nullif(sum(csd.summary), 0)       AS p20_fo,
        sum(w9) * 100.0 / nullif(sum(csd.summary), 0)       AS p22_fo,
        sum(w10) * 100.0 / nullif(sum(csd.summary), 0)      AS p24_fo,
        sum(w11) * 100.0 / nullif(sum(csd.summary), 0)      AS p26_fo,
        sum(w12) * 100.0 / nullif(sum(csd.summary), 0)      AS p28_fo,
        sum(w13) * 100.0 / nullif(sum(csd.summary), 0)      AS p30_fo
    FROM cte_sf_data csd
    CROSS JOIN cte_total_by_rf rf
    GROUP BY fo_group
)

SELECT
    csd.rn,
    csd.total_group,
    csd.fo_group,
    csd.sf_name,
    csd.fo_name,
    nullif(csd.summary, 0)          AS summary,
    nullif(csd.in_ais, 0)           AS in_ais,
    nullif(csd.p4, 0)               AS p4,
    nullif(csd.p6, 0)               AS p6,
    nullif(csd.p8, 0)               AS p8,
    nullif(csd.p10, 0)              AS p10,
    nullif(csd.p12, 0)              AS p12,
    nullif(csd.w1, 0)               AS w1,
    nullif(csd.w2, 0)               AS w2,
    nullif(csd.w3, 0)               AS w3,
    nullif(csd.w4, 0)               AS w4,
    nullif(csd.w5, 0)               AS w5,
    nullif(csd.w6, 0)               AS w6,
    nullif(csd.w7, 0)               AS w7,
    nullif(csd.w8, 0)               AS w8,
    nullif(csd.w9, 0)               AS w9,
    nullif(csd.w10, 0)              AS w10,
    nullif(csd.w11, 0)              AS w11,
    nullif(csd.w12, 0)              AS w12,
    nullif(csd.w13, 0)              AS w13,
    nullif(csd.treated, 0)          AS treated,
    nullif(csd.days_for_reply, 0)   AS days_for_reply,
    nullif(csd.oiv, 0)              AS oiv,
    nullif(csd.control, 0)          AS control,
    nullif(csd.control_50, 0)       AS control_50,
    coalesce(rf.p4_rf, 0)           AS p4_rf,
    coalesce(rf.p6_rf, 0)           AS p6_rf,
    coalesce(rf.p8_rf, 0)           AS p8_rf,
    coalesce(rf.p10_rf, 0)          AS p10_rf,
    coalesce(rf.p12_rf, 0)          AS p12_rf,
    coalesce(rf.p14_rf, 0)          AS p14_rf,
    coalesce(rf.p16_rf, 0)          AS p16_rf,
    coalesce(rf.p18_rf, 0)          AS p18_rf,
    coalesce(rf.p20_rf, 0)          AS p20_rf,
    coalesce(rf.p22_rf, 0)          AS p22_rf,
    coalesce(rf.p24_rf, 0)          AS p24_rf,
    coalesce(rf.p26_rf, 0)          AS p26_rf,
    coalesce(rf.p28_rf, 0)          AS p28_rf,
    coalesce(rf.p30_rf, 0)          AS p30_rf,
    coalesce(fo.p4_fo, 0)           AS p4_fo,
    coalesce(fo.p6_fo, 0)           AS p6_fo,
    coalesce(fo.p8_fo, 0)           AS p8_fo,
    coalesce(fo.p10_fo, 0)          AS p10_fo,
    coalesce(fo.p12_fo, 0)          AS p12_fo,
    coalesce(fo.p14_fo, 0)          AS p14_fo,
    coalesce(fo.p16_fo, 0)          AS p16_fo,
    coalesce(fo.p18_fo, 0)          AS p18_fo,
    coalesce(fo.p20_fo, 0)          AS p20_fo,
    coalesce(fo.p22_fo, 0)          AS p22_fo,
    coalesce(fo.p24_fo, 0)          AS p24_fo,
    coalesce(fo.p26_fo, 0)          AS p26_fo,
    coalesce(fo.p28_fo, 0)          AS p28_fo,
    coalesce(fo.p30_fo, 0)          AS p30_fo
FROM cte_sf_data csd
CROSS JOIN cte_total_by_rf rf
JOIN cte_total_by_fo fo ON fo.fo_group = csd.fo_group
ORDER BY csd.rn




-- огигинал


with osn as (with hands AS (
	     select * from handlings where created_date::text between '2014-11-01' AND '2019-09-06' and status_id !=6)
                 select gt.name
			,gt.id
			,gt.parent_geo_tag_id
			,gt.level
			,case when gt.level=1 then gt.id when gt.level=2 then gt.parent_geo_tag_id end as rn_parent_geo_tag_id
			,case when (sum (case when h.category_id = 62   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 62   then 1 else 0 end))::INT end as w1 --Сроки
			,case when (sum (case when h.category_id = 67   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 67   then 1 else 0 end))::INT end as w2 --Иное
			,case when (sum (case when h.category_id = 65   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 65   then 1 else 0 end))::INT end as w3 --Соблюдение прав граждан
			,case when (sum (case when h.category_id = 64   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 64   then 1 else 0 end))::INT end as w4 --Условия проживания
			,case when (sum (case when h.category_id = 63   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 63   then 1 else 0 end))::INT end as w5 --Качество
			,case when (sum (case when h.category_id = 1729 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 1729 then 1 else 0 end))::INT end as w6 --Запрос недостоверности
			,case when (sum (case when h.category_id = 850  then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 850  then 1 else 0 end))::INT end as w7 --Капремонт
			,case when (sum (case when h.category_id = 66   then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 66   then 1 else 0 end))::INT end as w8 --Работа с населением
			,case when (sum (case when h.category_id = 1251 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 1251 then 1 else 0 end))::INT end as w9 --ТКО
			,case when (sum (case when h.category_id = 2660 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 2660 then 1 else 0 end))::INT end as w10 --Сроки проведения капитального ремонта
			,case when (sum (case when h.category_id = 2661 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 2661 then 1 else 0 end))::INT end as w11 --Качество проведенного капитального ремонта
			,case when (sum (case when h.category_id = 2662 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 2662 then 1 else 0 end))::INT end as w12 --Взносы на капитальный ремонт
			,case when (sum (case when h.category_id = 2663 then 1 else 0 END))::INT = '0' then null else (sum (case when h.category_id = 2663 then 1 else 0 end))::INT end as w13 --Разъяснение Законодательства
			,count (*) /*filter (where email like 'rybakova@fondgkh.ru')*/ w0
		from geo_tags gt
		left join hands h on gt.id = h.region_id
		where gt.level in (2,1) --and created_date is not null
		group by gt.name, gt.level, gt.id, gt.parent_geo_tag_id
	    ),

treated as (select gt2.geo_tag_id as id
		   ,count(gt2.geo_tag_name) as treated --Рассмотрено
	    from(select h.id
			,h.status_id
			,h.surname
			,h.name
			,g.name geo_tag_name
			,g.level geo_tag_level
			,g.parent_geo_tag_id parent_geo_tag_id
			,g.id geo_tag_id
		 from handlings h
		 join handlings_status_changes_relations scr on h.id = scr.handling_id
		 join status_changes sc on scr.statuschange_id = sc.id and sc.is_successfully = 'true'
		 join (select hr.created ,hr.handling_id
		       from handling_replies hr
		       inner join (select id,max(created) over (partition by handling_id) = created is_last_created from handling_replies hr) t on hr.id = t.id where t.is_last_created) hr on h.id = hr.handling_id
		 left join geo_tags g on h.region_id = g.id
		 where h.created_date::text between '2014-11-01' AND '2019-09-06' and h.status_id = 9 and hr.created :: TEXT between '2014-11-01' AND '2019-09-06'
		 group by h.id, g.id
		 having count(case when sc.to_status_id = 21 then TRUE else null end) = 0
		 order by h.id) gt2
	    group by gt2.geo_tag_level, gt2.parent_geo_tag_id, gt2.geo_tag_id
	    ),

days_for_reply as(select gt2.id
			,round(avg(nullif(to_char(hr.created-h.created_date, 'DD'), '')::int),0) as days_for_reply --Время рассмотрения
		  from handlings h
		  join (select hr.created, hr.handling_id from handling_replies hr inner join (select id, max(created) over (partition by handling_id) = created is_last_created from handling_replies hr) t on hr.id = t.id where t.is_last_created) hr on h.id = hr.handling_id
		  join geo_tags gt2 ON gt2.id = h.region_id
		  where h.status_id in (9,21) and hr.created::text between '2014-11-01' AND '2019-09-06' and category_id != 1729 and NULLIF(to_char(hr.created-h.created_date, 'DD'), '')::int < 90
		  group by gt2.id,gt2.level,gt2.parent_geo_tag_id
		  ),

oiv as (select gt2.id
	       ,count(DISTINCT h.id) as oiv --Перенаправлено в ОИВ
	from handlings h
	join handlings_status_changes_relations hscr on hscr.handling_id = h.id
	join (select sc.id, sc.to_status_id, sc.created_at, sc.is_successfully from status_changes sc
	      inner join (select id, max(created_at) over (partition by hscr.handling_id, sc.to_status_id) = sc.created_at is_last_created
                          from status_changes sc
                          inner join handlings_status_changes_relations hscr on sc.id = hscr.statuschange_id) t	on t.id = sc.id
              where t.is_last_created) sc on hscr.statuschange_id = sc.id and sc.is_successfully = 'true'
        join statuses s on s.id = h.status_id
	left join geo_tags gt2 on h.region_id = gt2.id
	where h.created_date :: TEXT between '2014-11-01' AND '2019-09-06' and sc.created_at :: TEXT between '2014-11-01' AND '2019-09-06' AND sc.to_status_id = 21
	group by gt2.id,gt2.name
       ),

control as (select gt2.id
		   ,count(h.*) as control --На контролек
	    from handlings h
	    left join geo_tags gt2 on h.region_id = gt2.id
	    where h.status_id = 21 and h.created_date::date between '2014-11-01' AND '2019-09-06'
	    group by gt2.id
	   ),

control_50 as (select gt2.id
		      ,count(h.*) as control_50 --На контроле 50
	       from handlings h
	       left join geo_tags gt2 on h.region_id = gt2.id
               WHERE h.status_id = 21 and h.created_date::date between '2014-11-01' AND '2019-09-06' and (now()::date - created_date::date >50)
               group by gt2.id
              ),

it_sum as(select  rank() over (order by w.rn_parent_geo_tag_id,w.id) as rn
		  ,w.id
		  ,w.name
	          ,coalesce(sum(w.w0),0)+coalesce(sum(w.w1),0)+coalesce(sum(w.w2),0)+coalesce(sum(w.w3),0)+coalesce(sum(w.w4),0)+coalesce(sum(w.w5),0)+coalesce(sum(w.w6),0)+coalesce(sum(w.w7),0)++coalesce(sum(w.w8),0)+coalesce(sum(w.w9),0)+coalesce(sum(w.w10),0)+coalesce(sum(w.w11),0)+coalesce(sum(w.w12),0)+coalesce(sum(w.w13),0)as summa
	  from osn w
	  group by  w.rn_parent_geo_tag_id
		    ,w.id
	            ,w.name
	  order by rn
	 )

select  it.rn
		,w.name --1 ( № столбца из шаблона)
		,case when it.summa = 0 then null end --2
		,case when w.w0 = 0 then null else w.w0 end --3
		,w.w1 --5
		,w.w2 --7
		,w.w3 --9
		,w.w4 --11
		,w.w5 --13
		,w.w6 --15
		,w.w7 --17
		,w.w8 --19
		,w.w9 --21
		,w.w10 --23
		,w.w11 --25
		,w.w12 --27
		,w.w13 --29
		,t.treated --31
		,dfr.days_for_reply --32
		,o.oiv --33
		,control --34
		,control_50 --35
from osn w
join it_sum it on it.id=w.id
left join treated t on t.id=w.id
left join days_for_reply dfr on dfr.id=t.id
left join oiv o on o.id=dfr.id
left join control c on  c.id=o.id
left join control_50 c50 on c50.id=c.id
order by it.rn