;with --парсим имя портфеля
    p1 as (
        select
            p.id p_id,
            p.parent_id p_pid,
            cast(p.sign_date as date) 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 != 49
    )
    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
            debt as d
        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) --с типом 1,2 и блокированных
                            and a.parent_id = d.parent_id
                    )
                    and a.parent_id = d.parent_id
            )
            and not exists ( --и не содержит
                select po.d_id
                from #pisem_otpravleno po --дел по которым рассылка была
                where po.d_id = d.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 --и дата норм > чем дата подписания портф
            )
            and not exists ( --не содержит
                select po.d_id
                from #pisem_otpravleno po --дел по которым рассылка была
                where po.d_id = d.id
            )
    )
    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 po.d_id
                from #pisem_otpravleno po --дел по которым рассылка была
                where po.d_id = d.id
            )
    )
    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 po.d_id
                from #pisem_otpravleno po --дел по которым рассылка была
                where po.d_id = d.id
            )
    )
    select * into #d_ne_otpr from d6
-----------------------------------------------------
select
    (select name from bank b where b.id = #p.p_pid) Bank
    ,#p.Imya_portf_1 Imya_portf
    ,#p.Dt_Pokupki Dt_pokupki
    ,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, #p.p_pid, #p.Dt_Pokupki

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