daily pastebin goal
7%
SHARE
TWEET

Untitled

a guest Nov 14th, 2017 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ;with --парсим имя портфеля
  2.     p1 as (
  3.         select
  4.             p.id p_id,
  5.             p.parent_id p_pid,
  6.             right(p.contract_info, 12) Dt_Pokupki
  7.             ,(case
  8.                 when p.name like '%soft%'
  9.                     then SUBSTRING(p.name,1,PATINDEX('%__soft%',p.name))
  10.                 when p.name like '%hard%'
  11.                     then SUBSTRING(p.name,1,PATINDEX('%__hard%',p.name))
  12.                 when p.name like '%legal%'
  13.                     then SUBSTRING(p.name,1,PATINDEX('%__legal%',p.name))
  14.                 when p.name like '%exec%'
  15.                     then SUBSTRING(p.name,1,PATINDEX('%__exec%',p.name))
  16.             end) Imya_portf_1
  17.  
  18.         from portfolio p
  19.         where p.contract_typ = 2 and p.parent_id = 92
  20.     )
  21.     select * into #p from p1
  22. -----------------------------------------------------
  23. ;with --дел всего (для дел куплено)
  24.     d as (
  25.         select d.id d_id, d.r_portfolio_id d_rporft
  26.         from debt d
  27.     )
  28.     select * into #d_kupleno from d
  29. -----------------------------------------------------
  30. ;with --дел в работе
  31.     d1 as (
  32.         select d.id d_id, d.r_portfolio_id d_rporft
  33.         from debt d
  34.         where d.[status] not in (6,7,8,10) --не в статусах
  35.     )
  36.     select * into #d_v_rabote from d1
  37. -----------------------------------------------------
  38. ;with --находим долги кому письмо отправено
  39.     d2 as (
  40.         select cl.r_debt_id d_id
  41.         from
  42.             contact_log cl
  43.         where
  44.             cl.typ = 6 --с типом письмо
  45.             and (
  46.                 cl.dsc like '%Уведомление о состоявшейся уступке права требования%' --и с таким комментарием
  47.                 or cl.dsc like '%Уведомление о переуступке права требования%' -- или с таким комментарием
  48.             )
  49.     )
  50.     select * into #pisem_otpravleno from d2
  51. -----------------------------------------------------
  52. ;with -- выбираем долги без адресов (факт/рег)
  53.     d3 as (
  54.         select d.id d_id
  55.         from
  56.             portfolio as p
  57.             inner join debt as d on p.id = d.r_portfolio_id
  58.             --inner join address a on a.parent_id = d.parent_id
  59.         where
  60.             not exists ( --не содержит
  61.                 select a.parent_id
  62.                 from address a --адресов
  63.                 where
  64.                     a.typ in (1,2) --с типом (рег/факт)
  65.                     and not exists (
  66.                         select a.parent_id
  67.                         from address ad
  68.                         where (ad.typ in (1,2) and a.block_flag = 1)
  69.                             and a.parent_id = d.parent_id
  70.                     )
  71.                     and a.parent_id = d.parent_id
  72.             )
  73.     )
  74.     select * into #d_bez_adr from d3
  75. -----------------------------------------------------
  76. ;with -- долги с не нормализированными адресами (факт/рег)
  77.     d4 as (
  78.         select
  79.             d.id d_id
  80.         from
  81.             portfolio as p
  82.             inner join debt as d on p.id = d.r_portfolio_id
  83.         where
  84.             exists (
  85.                 select a.parent_id
  86.                 from  address a
  87.                 where a.typ in (1,2)
  88.                         and (a.block_flag = 0 or block_flag is null)    --не блокирован
  89.                         and (
  90.                             a.verification_dt <= p.sign_date or --верификац ранее входа портф или
  91.                             a.verification_dt is null   --верификац отсутств.
  92.                         )
  93.                         and a.parent_id = d.parent_id
  94.             )
  95.             and not exists ( --не содержит
  96.                 select a.parent_id
  97.                 from address a --адреса
  98.                 where a.parent_id = d.parent_id
  99.                     and a.typ in (1,2) --с типом (рег/факт)
  100.                     and (a.block_flag = 0 or block_flag is null) --не блокирован
  101.                     and a.verification_dt >= p.sign_date --дата норм > чем дата подписания портф
  102.             )
  103.     )
  104.     select * into #d_adr_bez_norm from d4
  105. -----------------------------------------------------
  106. ;with -- долги с нормализированными адресами (рег/факт) на которые не было рассылки
  107.     d5 as (
  108.         select
  109.             d.id d_id
  110.         from
  111.             portfolio as p
  112.             inner join debt as d on p.id = d.r_portfolio_id
  113.         where
  114.             exists (
  115.                 select a.parent_id
  116.                 from address a
  117.                 where
  118.                     a.typ in (1,2) --рег/факт
  119.                     and a.verification_dt >= p.sign_date --дата  нормализ  > дата входа портф
  120.                     and (
  121.                         a.block_flag = 0 or  -- адрес не блокирован
  122.                         block_flag is null  -- адрес не блокирован
  123.                     )
  124.                     and a.parent_id = d.parent_id
  125.             )
  126.             and not exists ( --не содержит
  127.                 select c.r_debt_id
  128.                 from contact_log c --контакты
  129.                 where
  130.                     c.r_debt_id = d.id
  131.                     and (
  132.                         c.dsc like '%Уведомление о состоявшейся уступке права требования%' --с таким комментарием
  133.                         or c.dsc like '%Уведомление о переуступке права требования%' --или с таким комментарием
  134.                     )
  135.             )
  136.     )
  137.     select * into #d_norm_adr_net_rassilki from d5
  138. -----------------------------------------------------
  139. ;with --долги по котрым рассылки писем не было
  140.     d6 as (
  141.         select d.id d_id
  142.         from
  143.             debt as d
  144.         where
  145.             not exists ( --не содержит
  146.                 select c.r_debt_id
  147.                 from contact_log c --контактов
  148.                 where (
  149.                     c.dsc like '%Уведомление о состоявшейся уступке права требования%' --с таким комментарием
  150.                     or c.dsc like '%Уведомление о переуступке права требования%' --или с таким комментарием
  151.                     )
  152.                     and c.r_debt_id = d.id
  153.             )
  154.     )
  155.     select * into #d_ne_otpr from d6
  156. -----------------------------------------------------
  157. select
  158.     #p.Imya_portf_1 Imya_portf
  159.     ,count(#d_kupleno.d_id) Del_kupleno
  160.     ,count(#d_v_rabote.d_id) Del_v_rabote
  161.     ,count(#pisem_otpravleno.d_id) Del_komy_otpravleno
  162.     ,count(#d_ne_otpr.d_id) Del_komy_ne_otpravleno
  163.     ,count(#d_bez_adr.d_id) Del_bez_adresov
  164.     ,count(#d_adr_bez_norm.d_id) Del_bez_normal_adresov
  165.     ,count(#d_norm_adr_net_rassilki.d_id) Del_s_norm_adr_bez_pisem
  166.    
  167. from
  168.     #p
  169.     inner join #d_kupleno on #d_kupleno.d_rporft = #p.p_id
  170.     left join #d_v_rabote on #d_v_rabote.d_id = #d_kupleno.d_id
  171.     left join #pisem_otpravleno on #pisem_otpravleno.d_id = #d_kupleno.d_id
  172.     left join #d_bez_adr on #d_bez_adr.d_id = #d_kupleno.d_id
  173.     left join #d_adr_bez_norm on #d_adr_bez_norm.d_id = #d_kupleno.d_id
  174.     left join #d_norm_adr_net_rassilki on #d_norm_adr_net_rassilki.d_id = #d_kupleno.d_id
  175.     left join #d_ne_otpr on #d_ne_otpr.d_id = #d_kupleno.d_id
  176.  
  177. group by
  178.     #p.Imya_portf_1
  179.  
  180. order by #p.Imya_portf_1
  181.    
  182.  
  183. 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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top