---

SELECT
td.year as year,
case td.type
when 'NONE' then 'Нет справки'
when 'NOT_RECEIVED' then 'Вычет не получен'
when 'RECEIVED' then 'Вычет получен'
end as type,
td.sum_income as sum_income,
to_char(td.date, 'DD.MM.YYYY') as date,
td.sum_tax_deduction as sum_tax_deduction,
td.max_tax_deduction as max_tax_deduction
FROM ourpension.year_tax_deduction td
left join ourpension.application_termination_contract atc on td.application_termination_contract_id = atc.id
LEFT JOIN ourpension.application_personal_data_individual apdi on apdi.id = atc.application_personal_data_individual_id
left join ourpension.application_termination_contract_data atcd on atcd.application_termination_contract_id = atc.id
LEFT JOIN ourpension.sharer sh ON sh.id = atcd.sharer_id
LEFT JOIN ourpension.contract contract ON contract.id = sh.contract_id
WHERE td.year >= cast(to_char(contract.date, 'YYYY') as int)

and td.type != 'NONE'

select case when canceled is null then 'Актуальная' else 'Неактуальная' end, year, dt, t."date",
        max_tax_deduction, sum_income, sum_tax_deduction, case when type = 'NONE' then 'НЕТ' else type end,
        number
from (SELECT
id, "date",max_tax_deduction,sum_income,sum_tax_deduction,"type","version",
"year",application_termination_contract_id,individual_id,id_1c,sharer_id_1c,
person_id,reason_doc_id_1c, "number","comment",agent_id,file,file_name,
tessa_card_id,canceled,accepted, row_number()over(partition by x."year" order by x.version desc) as dt
FROM ourpension.year_tax_deduction x
WHERE application_termination_contract_id = :appId --60334019

    and x.canceled is not null
) t