---

select
    cast(o.ef_year as varchar(4)) as year_name,
    concat(ot.name, ' ', sh.number) as name,
    sum(o.value) as sum_value,
    sum(n.value) as sum_ndfl
from operations o
join back_office.operation_type ot
    on ot.id = o.operation_type_id
left join operations n
    on o.id = n.id
   and n.cred_mnemonics like '%НДФЛ%'
left join ourpension.sharer sh
    on o.owner_sharer_id = sh.id
where o.deb_mnemonics is not null
  and o.deb_mnemonics not like '%СВ%'
  and o.deb_mnemonics not like '%ПП%'
  and ot.name not like '%Выплата%'
  and o.operation_type_id <> 106
  and coalesce(o.cred_mnemonics, '') not like '%НДФЛ%'
group by o.ef_year, ot.name, sh.number
order by o.ef_year, ot.name, sh.number;