---

select
    s.id as sharer_id,
    s.number as sharer_number,

    o.id as operation_id,
    t.effective_date as transaction_date,
    o.effective_date as operation_date,
    t.value,

    ot.code as operation_code,
    ot.short_name as operation_name,
    o.approved,
    o.deleted,

    da.id as debit_account_id,
    dat.mnemonics as debit_account_type,
    datg.mnemonics as debit_account_group,
    da.owner_sharer_id as debit_sharer_id,

    ca.id as credit_account_id,
    cat.mnemonics as credit_account_type,
    catg.mnemonics as credit_account_group,
    ca.owner_sharer_id as credit_sharer_id

from back_office.transaction t
join back_office.operation o
    on o.id = t.operation_id
join back_office.operation_type ot
    on ot.id = o.operation_type_id

left join back_office.account da
    on da.id = t.debit_account_id
left join back_office.account_type dat
    on dat.id = da.account_type_id
left join back_office.account_type_group datg
    on datg.id = dat.account_type_group_id

left join back_office.account ca
    on ca.id = t.credit_account_id
left join back_office.account_type cat
    on cat.id = ca.account_type_id
left join back_office.account_type_group catg
    on catg.id = cat.account_type_group_id

join ourpension.sharer s
    on (
        s.id = da.owner_sharer_id
        or s.id = ca.owner_sharer_id
    )

where s.number = 'НВУ-0177000059-06/10-000063-00857634061'
  and (
      cast(t.effective_date as date) in (
          date '2023-05-31',
          date '2020-03-30'
      )
      or cast(o.effective_date as date) in (
          date '2023-05-31',
          date '2020-03-30'
      )
  )

order by t.effective_date, o.id, t.id;