daily pastebin goal
64%
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
Top