;with --парсим имя портфеля
    p1 as (
        select
            p.id p_id,
            p.parent_id p_pid,
            right(p.contract_info, 12) Dt_Pokupki
            ,(case
                when p.name like '%soft%'
                    then SUBSTRING(p.name,1,PATINDEX('%__soft%',p.name))
                when p.name like '%hard%'
                    then SUBSTRING(p.name,1,PATINDEX('%__hard%',p.name))
                when p.name like '%legal%'
                    then SUBSTRING(p.name,1,PATINDEX('%__legal%',p.name))
                when p.name like '%exec%'
                    then SUBSTRING(p.name,1,PATINDEX('%__exec%',p.name))
            end) Imya_portf_1

        from portfolio p
        where p.contract_typ = 2 and p.parent_id = 92
    )
    select * into #p from p1
-----------------------------------------------------
;with --дел всего (для дел куплено)
    d as (
        select d.id d_id, d.r_portfolio_id d_rporft
        from debt d
    )
    select * into #d_kupleno from d
-----------------------------------------------------
;with --дел в работе
    d1 as (
        select d.id d_id, d.r_portfolio_id d_rporft
        from debt d
        where d.[status] not in (6,7,8,10) --не в статусах
    )
    select * into #d_v_rabote from d1
-----------------------------------------------------
;with --находим долги кому письмо отправено
    d2 as (
        select cl.r_debt_id d_id
        from
            contact_log cl
        where
            cl.typ = 6 --с типом письмо
            and (
                cl.dsc like '%Уведомление о состоявшейся уступке права требования%' --и с таким комментарием
                or cl.dsc like '%Уведомление о переуступке права требования%' -- или с таким комментарием
            )
    )
    select * into #pisem_otpravleno from d2
-----------------------------------------------------
;with -- выбираем долги без адресов (факт/рег)
    d3 as (
        select d.id d_id
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
            --inner join address a on a.parent_id = d.parent_id
        where
            not exists ( --не содержит
                select a.parent_id
                from address a --адресов
                where
                    a.typ in (1,2) --с типом (рег/факт)
                    and not exists (
                        select a.parent_id
                        from address ad
                        where (ad.typ in (1,2) and a.block_flag = 1)
                            and a.parent_id = d.parent_id
                    )
                    and a.parent_id = d.parent_id
            )
    )
    select * into #d_bez_adr from d3
-----------------------------------------------------
;with -- долги с не нормализированными адресами (факт/рег)
    d4 as (
        select
            d.id d_id
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
        where
            exists (
                select a.parent_id
                from  address a
                where a.typ in (1,2)
                        and (a.block_flag = 0 or block_flag is null)    --не блокирован
                        and (
                            a.verification_dt <= p.sign_date or --верификац ранее входа портф или
                            a.verification_dt is null   --верификац отсутств.
                        )
                        and a.parent_id = d.parent_id
            )
            and not exists ( --не содержит
                select a.parent_id
                from address a --адреса
                where a.parent_id = d.parent_id
                    and a.typ in (1,2) --с типом (рег/факт)
                    and (a.block_flag = 0 or block_flag is null) --не блокирован
                    and a.verification_dt >= p.sign_date --дата норм > чем дата подписания портф
            )
    )
    select * into #d_adr_bez_norm from d4
-----------------------------------------------------
;with -- долги с нормализированными адресами (рег/факт) на которые не было рассылки
    d5 as (
        select
            d.id d_id
        from
            portfolio as p
            inner join debt as d on p.id = d.r_portfolio_id
        where
            exists (
                select a.parent_id
                from address a
                where
                    a.typ in (1,2) --рег/факт
                    and a.verification_dt >= p.sign_date --дата  нормализ  > дата входа портф
                    and (
                        a.block_flag = 0 or  -- адрес не блокирован
                        block_flag is null  -- адрес не блокирован
                    )
                    and a.parent_id = d.parent_id
            )
            and not exists ( --не содержит
                select c.r_debt_id
                from contact_log c --контакты
                where
                    c.r_debt_id = d.id
                    and (
                        c.dsc like '%Уведомление о состоявшейся уступке права требования%' --с таким комментарием
                        or c.dsc like '%Уведомление о переуступке права требования%' --или с таким комментарием
                    )
            )
    )
    select * into #d_norm_adr_net_rassilki from d5
-----------------------------------------------------
;with --долги по котрым рассылки писем не было
    d6 as (
        select d.id d_id
        from
            debt as d
        where
            not exists ( --не содержит
                select c.r_debt_id
                from contact_log c --контактов
                where (
                    c.dsc like '%Уведомление о состоявшейся уступке права требования%' --с таким комментарием
                    or c.dsc like '%Уведомление о переуступке права требования%' --или с таким комментарием
                    )
                    and c.r_debt_id = d.id
            )
    )
    select * into #d_ne_otpr from d6
-----------------------------------------------------
select
    #p.Imya_portf_1 Imya_portf
    ,count(#d_kupleno.d_id) Del_kupleno
    ,count(#d_v_rabote.d_id) Del_v_rabote
    ,count(#pisem_otpravleno.d_id) Del_komy_otpravleno
    ,count(#d_ne_otpr.d_id) Del_komy_ne_otpravleno
    ,count(#d_bez_adr.d_id) Del_bez_adresov
    ,count(#d_adr_bez_norm.d_id) Del_bez_normal_adresov
    ,count(#d_norm_adr_net_rassilki.d_id) Del_s_norm_adr_bez_pisem

from
    #p
    inner join #d_kupleno on #d_kupleno.d_rporft = #p.p_id
    left join #d_v_rabote on #d_v_rabote.d_id = #d_kupleno.d_id
    left join #pisem_otpravleno on #pisem_otpravleno.d_id = #d_kupleno.d_id
    left join #d_bez_adr on #d_bez_adr.d_id = #d_kupleno.d_id
    left join #d_adr_bez_norm on #d_adr_bez_norm.d_id = #d_kupleno.d_id
    left join #d_norm_adr_net_rassilki on #d_norm_adr_net_rassilki.d_id = #d_kupleno.d_id
    left join #d_ne_otpr on #d_ne_otpr.d_id = #d_kupleno.d_id

group by
    #p.Imya_portf_1

order by #p.Imya_portf_1


drop table #p, #d_kupleno, #d_v_rabote, #pisem_otpravleno, #d_bez_adr, #d_adr_bez_norm, #d_norm_adr_net_rassilki,#d_ne_otpr