ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql

sql

原创 Linux操作系统 作者:keeking 时间:2012-08-08 18:00:56 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ibm 摩根
请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    242153