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/