sql

SELECT 

 c.CONTRACT_ID,
 c.CONTRACT_NBR,
 gc.GUARANTY_COMPANY_NAME AS gc,
 decode(c.BANK_CONTRACT_ID, 472, 'cfc', 'other') AS model,
 c.PAYOFF_DATE,
 c.person_id,
 rg.REGION_NAME AS SELLERPLACE_REGION,
 c.CONTRACT_STATUS_3 AS STATUS,
 a.UNPAID_PENALTY,
 a.REMAINING_INST,

  (SELECT 
          trunc(SYSDATE) - MIN(i.DATE_INSTALMENT) AS dpd
   FROM   dwcn.instalment i
    where    i.instalment_TYPE = 'R'
   AND    i.PAYMENT_AMOUNT < i.INSTALMENT_AMOUNT
   ) as dpd

FROM   dwcn.contract c
JOIN   dwcn.salroom s ON c.SALROOM_ID = s.SALROOM_ID
JOIN   dwcn.hc_region rg ON s.HCREGION_ID = rg.HCREGION_ID
JOIN   dwcn.bank_contract bc ON c.BANK_CONTRACT_ID = bc.BANK_CONTRACT_ID
JOIN   dwcn.guaranty_company gc ON bc.guaranty_company_id = gc.guaranty_company_id
join   
(SELECT 
  i.CONTRACT_ID,
 SUM(CASE
         WHEN i.instalment_TYPE = 'P' THEN
          i.INSTALMENT_AMOUNT - nvl(PAYMENT_AMOUNT, 0)
         ELSE
          0
     END) AS UNPAID_PENALTY, 
 SUM(CASE
         WHEN i.instalment_TYPE = 'R' THEN
          i.INSTALMENT_AMOUNT - nvl(PAYMENT_AMOUNT, 0)
         ELSE
          0
     END) AS REMAINING_INST
     
FROM   dwcn.instalment i
group by  i.CONTRACT_ID) a on a.CONTRACT_ID=c.CONTRACT_ID

join (SELECT t.person_id,             
              substr(MAX(CASE WHEN rel2person_code = 1 AND contact_code = 2 AND contact_status = 'a' and is_contact_phone=1 THEN to_char(dtime_modiftime, 'yyyy-mm-dd hh24:mi:ss') || nvl(contact, '') ELSE NULL END), 20, 20) AS contact_mobil
      FROM   (SELECT /*a.id_source,*/
               a.person_id,
               a.contact,
               a.area_code,
               a.contact_status,
               a.contact_ext,
               b.contact_code, /*b.contact_name,*/
               c.rel2person_code, /*c.rel2person_name,*/
               a.dtime_modiftime,
               a.owner_name,
               a.is_contact_phone,
               RANK() over(PARTITION BY a.person_id, b.contact_code, c.rel2person_code, a.contact_status,a.is_contact_phone ORDER BY a.dtime_modiftime DESC) rn
              FROM   dwcn.t_contact a
              JOIN   dwcn.clt_contact_type b ON a.contact_type_id = b.contact_type_id
              JOIN   dwcn.clt_rel2person_type c ON a.rel2person_type_id = c.rel2person_type_id
              WHERE  a.source_table = 'C'
              ) t
      WHERE  rn = 1
      GROUP  BY t.person_id) b on b.person_id=c.person_id

WHERE  c.CONTRACT_STATUS_3 IN ('a', 'k')
and    c.CONTRACT_ID='7675157'

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-740402/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/7899089/viewspace-740402/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值