---

WITH q AS (
    SELECT DISTINCT ON (c.number)
        i.last_name as last_name,
        i.first_name as first_name,
        i.middle_name as middle_name,
        c.number as contract_number,
        to_char(c.date, 'DD.MM.YYYY') as contract_date,
        atc."date" as atc_date,
        atc.id as atc_id
    FROM ourpension.application_termination_contract atc
    left join ourpension.year_tax_deduction td 
        on td.application_termination_contract_id = atc.id
    left join ourpension.individual i 
        on i.id = atc.individual_id
    left join ourpension.fund f 
        on i.fund_id = f.id
    left join ourpension.sharer s 
        on s.individual_id = atc.individual_id
    left join ourpension.contract c 
        on s.contract_id = c.id 
        and (c."service_type" = 'NPO' or c."service_type" = 'PDS')
    left join ourpension.uv_activity_type uat
        on uat.activity_eng_type = c.service_type
        and uat.client_type = c.client_type
    where atc."date" >= to_date(:start_date_app, 'YYYY.MM.DD')
      and atc."date" < to_date(:end_date_app, 'YYYY.MM.DD') + interval '1 day'
      and (:isChecked = false or td.type != 'NONE')
      and i.first_name is not null
      and (uat.activity_rus_type = 'ПДС' or uat.activity_rus_type = 'НПО')
      and atc.id is not null
    ORDER BY c.number, atc."date" DESC, atc.id DESC
)
SELECT
    last_name,
    first_name,
    middle_name,
    contract_number,
    contract_date
FROM q
ORDER BY contract_number