---

select
    to_char(o.ef_date, 'DD.MM.YYYY') as effective_date,
    coalesce(ot.name_for_statement, ot.name) as name,
    sum(
        case
            when ot.code = 'PDS_RETURN_LUMP_SUM_PAYMENT'
                then -o.value
            when ot.code in (
                'PDS_NDFL_REDEMPTION_PAYMENT',
                'PDS_NDFL_LUMP_SUM_PAYMENT',
                'PDS_RETURN_NDFL_LUMP_SUM'
            )
                or coalesce(o.cred_mnemonics, '') like '%НДФЛ%'
                then null
            else o.value
        end
    ) as value,
    o.ef_date,
    sum(
        case
            when ot.code = 'PDS_RETURN_NDFL_LUMP_SUM'
                then -o.value
            when ot.code = 'PDS_RETURN_LUMP_SUM_PAYMENT'
                then null
            when ot.code in (
                'PDS_NDFL_REDEMPTION_PAYMENT',
                'PDS_NDFL_LUMP_SUM_PAYMENT'
            )
                or coalesce(o.cred_mnemonics, '') like '%НДФЛ%'
                then o.value
            else null
        end
    ) as ndfl
from operations o
join back_office.operation_type ot
    on ot.id = o.operation_type_id
    and ot.display_in_statement = true
    and ot.type_movement in (582, 583)
where o.deb_mnemonics is not null
group by
    o.ef_date,
    coalesce(ot.name_for_statement, ot.name)
order by o.ef_date;