declare @d_cont date = dateadd(m,-4/*(:dt)*/,cast(getdate() as date)) --за последний месяц

;with
    c as (
        select c.r_debt_id as cl_r_debt_id, c.dt as Dt_cont, c.id as cont_id, cr.[text] cont_text
        from i_collect.dbo.contact_log c
            left join i_collect.dbo.contact_result_set cr on cr.code = c.result
        where
            cr.[text] in (
                'В суд поданы документы на восстановление ИД',
                'В УСВД переданы документы на восстановление ИД',
                'Дубликат ИД',
                'Получен дубликат ИД',
                'Запрос справки об утере ИД',
                'Получено определение суда о выдаче дубликата',
                'Получен отказ в выдаче дубликата ИД',
                'Приняты документы на восстановление ИД',
                'Приняты судом документы на восстановление ИД',
                'Справка об утере ИД для получения дубликата',
                'Справка для получения дубликата'
            )
            and c.dt >= @d_cont
    )
    select * into #cont from c

select distinct
    d.id ID_dolga
    ,per.f+' '+per.i+' '+per.o FIO_d_ka
    ,b.name Bank
    ,p.name Portfel
    ,isnull(us_dep.oper_fio, 'Не установлен') Operator
    ,isnull(us_dep.dep_name, 'Не установлен') Podrazdelenie
    ,(select name from dict dt where dt.parent_id = 6 and dt.code = d.[status]) status_dolga
    ,d.debt_sum Ostatok
    ,isnull(last_plat.sum_plat, 0) summa_posl_plat
    ,cast(last_plat.dt_plat as date) dt_posl_plat
    ,cast(cont_zapors_spravki_ob_utere_ID.Dt_cont as date) Dt_zaprosa_v_FSSP_spravki
    ,cast(cont_spravka_ob_utere_ID.Dt_cont as date) Dt_polucheniya_spravki

    ,cast(cont_sposob_polucheniya.Dt_cont as date) Dt_peredachi_spravki_v_sud_USVD
    ,(case
        when cont_sposob_polucheniya.cont_text in (
            'В суд поданы документы на восстановление ИД',
            'Приняты документы на восстановление ИД',
            'Приняты судом документы на восстановление ИД'
        )
            then 'Через суд'
        when cont_sposob_polucheniya.cont_text = 'В УСВД переданы документы на восстановление ИД'
            then 'Через УСВД'
    end) sposob_polucheniya_docs

    ,cast(cont_poluch_opredelenie_suda.Dt_cont as date) Dt_poluch_opredeleniya_suda
    ,cast(cont_poluch_otkaz_dublicat.Dt_cont as date) Dt_okonch_procesa
    ,(case
        when cont_poluch_otkaz_dublicat.cont_text in ('Дубликат ИД', 'Получен дубликат ИД')
            then 'Получен дубликат ИД'
        when cont_poluch_otkaz_dublicat.cont_text = 'Получен отказ в выдаче дубликата ИД'
            then 'Получен отказ'
    end) Itog

from
    bank as b
    inner join portfolio as p on b.id = p.parent_id
    inner join debt as d on p.id = d.r_portfolio_id
    --контакты из временной таблицы
    inner join #cont c on c.cl_r_debt_id = d.id
    inner join person per on per.id = d.parent_id
    -- юзеры и их подразделения
    left join (
        select w.r_debt_id, u.f+' '+u.i+' '+u.o oper_fio, dep.name dep_name
        from work_task w
            left join users u on u.id = w.r_user_id
            left join department dep on dep.dep = u.r_department_id
    ) us_dep on us_dep.r_debt_id = d.id
    -- последние платежи и их даты
    left join (
        select c.parent_id, c.sum sum_plat, c.calc_date dt_plat
        from i_collect.dbo.debt_calc c
        where c.id in (
            select max(id)
            from i_collect.dbo.debt_calc
            group by parent_id
        )
        and (c.is_cancel = 0 or c.is_cancel is null)
    ) last_plat on last_plat.parent_id = d.id
--1-- последний контакт "Запрос справки об утере ИД" и "Справка для получения дубликата"
    left join (
        select c.cl_r_debt_id, c.Dt_cont
        from #cont c
        where c.cont_id in (
            select max(c_2.cont_id)
            from #cont c_2
            where c_2.cont_text in ('Запрос справки об утере ИД', 'Справка для получения дубликата')
            group by c_2.cl_r_debt_id
        )
    ) cont_zapors_spravki_ob_utere_ID on cont_zapors_spravki_ob_utere_ID.cl_r_debt_id = d.id
--2-- контакт "Cправка об утере ИД для получения дубликата"
    left join (
        select c.cl_r_debt_id, c.Dt_cont
        from #cont c
        where c.cont_id in (
            select max(c_2.cont_id)
            from #cont c_2
            where c_2.cont_text in ('Справка об утере ИД для получения дубликата', 'Справка для получения дубликата')
            group by c_2.cl_r_debt_id
        )
    ) cont_spravka_ob_utere_ID on cont_spravka_ob_utere_ID.cl_r_debt_id = d.id
--3/4-- последние контакты "В суд поданы документы на восстановление ИД и В УСВД переданы документы на восстановление ИД"
    left join (
        select c.cl_r_debt_id, c.cont_text, c.Dt_cont
        from #cont c
        where c.cont_id in (
            select max(c1.cont_id)
            from #cont c1
            where c1.cont_text in (
                    'В суд поданы документы на восстановление ИД',
                    'В УСВД переданы документы на восстановление ИД',
                    'Приняты документы на восстановление ИД',
                    'Приняты судом документы на восстановление ИД'
            )
            group by c1.cl_r_debt_id
        )
    ) cont_sposob_polucheniya on cont_sposob_polucheniya.cl_r_debt_id = d.id
--5-- контакт "Получено определение суда о выдаче дубликата"
    left join (
        select c.cl_r_debt_id, c.Dt_cont
        from #cont c
        where cont_text in ('Получено определение суда о выдаче дубликата')
    ) cont_poluch_opredelenie_suda on cont_poluch_opredelenie_suda.cl_r_debt_id = d.id
    -- контакт "Дубликат ИД", "Получен отказ в выдаче дубликата ИД"
    left join (
        select c.cl_r_debt_id, cont_text, c.Dt_cont
        from #cont c
        where c.cont_id in (
            select max(c1.cont_id)
            from #cont c1
            where c1.cont_text in ('Дубликат ИД', 'Получен дубликат ИД', 'Получен отказ в выдаче дубликата ИД')
            group by c1.cl_r_debt_id
        )
    ) cont_poluch_otkaz_dublicat on cont_poluch_otkaz_dublicat.cl_r_debt_id = d.id



where
    c.Dt_cont >= @d_cont
    and p.parent_id != 68

order by d.id

drop table #cont