---

with year_table (year_name) as (
    select extract(year from d)
    from generate_series(
        date_trunc('year', cast($P{bDate} as date)),
        date_trunc('year', cast($P{eDate} as date)),
        interval '1 year'
    ) as years(d)
),



operations (
    id, ef_year, cred_mnemonics, deb_mnemonics,
    value, name, acc_name, type_movement, operation_code
) as (



    ot.type_movement,
    ot.code




select
    cast(yt.year_name as varchar(4)) as year_name,
    count(p.id) as count_oper,
    sum(p.sum_value) as sum_value,
    sum(n.sum_ndfl) as sum_ndfl
from year_table yt

left join (
    select
        o.id,
        o.ef_year,
        sum(o.value) as sum_value
    from operations o
    where coalesce(o.cred_mnemonics, '') not like '%НДФЛ%'
      and (
          (
              o.deb_mnemonics is not null
              and o.deb_mnemonics not like '%СВ%'
              and o.deb_mnemonics not like '%ПП%'
              and o.name like '%Выплата%'
          )
          or o.operation_code = 'PDS_PERIODIC_PAYMENT'
      )
    group by o.id, o.ef_year
) p on p.ef_year = yt.year_name

left join (
    select
        id,
        sum(value) as sum_ndfl
    from operations
    where cred_mnemonics like '%НДФЛ%'
    group by id
) n on n.id = p.id

group by yt.year_name
order by yt.year_name;