create or replace function "bd"."function"("reg" int4, "date" date)
  returns table("4.1" int4, "5.1" int4, "7.1" int4, "8.1" int4, "9.1" int4, "10.1" int4, "11.1" int4, "12.1" int4, "10.1" int4, "14.1" int4, "15.1" int4, "18.1" int4, "19.1" int4) as $body$
with data as (
                select *
                        from (
                    select
                        ro.reg,
                        cc.date,
                        cd.age,
                        cd.status,
                        cc.hg,
                        cc.sp,
                        lda.cn,
                        lda.cdi,
                        cc.card,
                        row_number() over (partition by cd.pat, cd.tp, cd.age order by cd.sd desc) as rnt
                    from
                        cappa cd
                        left join rege.dea chsa on chsa.card = cd.id
                        left join rege.lea lda on lda.aft = chsa.id
                        left join rege.conc cc on cc.card = cd.id
                        left join rege.org lo on lo.id_org = cd.check
                        left join dr.ref ro on ro.oid = lo.aid
                                         
                    where status = 2
                    and type = 2
                    and categ = 4
                    and date IS NOT NULL
                ) as "Запрос" where rnt = 1
          )
          select
              reg,
              sum(case when age <= 1 then 1 else 0 end) - sum(case when cdi IN (99, 100) then 1 else 0 end) as "4.1",
              sum(case when age < 10 then 1 else 0 end) - sum(case when cdi IN (99, 100) AND age < 10 then 1 else 0 end) as "5.1",
              sum(case when hg = 1 then 1 else 0 end) as "7.1",
              sum(case when hg = 2 then 1 else 0 end) as "8.1",
              sum(case when hg = 3 then 1 else 0 end) as "9.1",
              sum(case when hg = 4 then 1 else 0 end) as "10.1",
              sum(case when hg = 5 then 1 else 0 end) as "11.1",
              sum(case when sp = 1 then 1 else 0 end) as "12.1",
              sum(case when sp = 2 then 1 else 0 end) as "10.1",
              sum(case when sp = 3 then 1 else 0 end) as "14.1",
              sum(case when sp = 4 then 1 else 0 end) as "15.1",
              sum(case when cn IN (99, 100) then 1 else 0 end) as "18.1",
              sum(case when cdi IN (99, 100) then 1 else 0 end) as "19.1"
          from data
          group by reg
$body$
  language sql stable strict security definer