select
    d.id
from
    portfolio as p
    inner join debt as d on p.id = d.r_portfolio_id
    inner join person as per on d.parent_id = per.id

-- u -- пользователи
    inner join(
        select wt.r_debt_id
        from work_task wt left join users u on u.id = wt.r_user_id
                        left join department dep on dep.dep = u.r_department_id
        where
            dep.name like '%Колл%' or
            dep.name like '%Хард%' or
            dep.name like '%УВВиИП%' or
            dep.name like '%УВВ%' or
            dep.name = 'УИП по СПБ и ЛО' or
            dep.name = 'Межрегиональное взыскание' or
            (u.id = -1 or
            u.f like '%Не установлен%') and -- без закрепления
            u.id not in (42,76) -- не Галицына, не Полосухин
    ) u on u.r_debt_id = d.id

-- pk -- Контактность
    inner join (
        select distinct cl.r_debt_id as r_debt_id
        from contact_log as cl
            inner join debt as d on cl.r_debt_id =d.id
            inner join portfolio as p on d.r_portfolio_id = p.id
        where
            ((p.parent_id in(82,80) and cast(p.sign_date as date)>'2016-01-01')
                or (p.parent_id in(49) and cast(p.sign_date as date) > '2016-09-01' ))
            and cl.result>100000000
            and (substring(cast(cl.result as varchar ),8,1)!= '9'
                or substring(cast(cl.result as varchar ),8,1)!= '6'
                or substring(cast(cl.result as varchar ),8,1)!= '7')
    ) pk on pk.r_debt_id = d.id

-- ph -- телефоны
    inner join (
        select parent_id, id, number n
        from phone
        where
            typ in (1, 2)
            and status !=3
            and (
                len(number) = 11
                or len(number2) = 11
            )
            and block_flag = 0
            and (
                SUBSTRING(number, 1, 1) in (7,8)
                or SUBSTRING(number2, 1, 1) in (7,8)
            )
            and (
                SUBSTRING(number, 2, 3) != 800
                or SUBSTRING(number2, 2, 3) != 800
            )
            )ph on
                ph.parent_id = per.id

where
    d.debt_sum >= 1000
    and (
        (p.parent_id in (80, 82) and --Сбер, Сетелм
        cast(p.sign_date as date) between '20160101' and '20171231') or --2016год
        (p.parent_id = 49 and -- БалтБанк
        cast(p.sign_date as date) between '20160901' and cast(getdate() as date)) -->
        )
    and d.[status] not in (6,7,8,9,10,14,17)

-- Список отказников за посл 2 мес.
    and d.id not in (
        select cl.r_debt_id
        from contact_log as cl
        where cl.result>100000000 and
            substring(cast(cl.result as varchar ),8,2)= '05'
            and cast(cl.dt as date) > dateadd(m,-2,cast(getdate() as date))
        group by cl.r_debt_id
    )

-- обещания > getdate()
    and d.id not in (
        select parent_id
        from debt_promise
        where prom_date>cast(getdate() as date)
        group by parent_id
    )