SHARE
TWEET

Untitled

pedravirada Jun 11th, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT TIPO_OPERACAO,
  2.        CASE WHEN COUNT(1) > 0 THEN COUNT(1) ELSE 0 END AS qtd_iniciado
  3.           ,SUM(
  4.             CASE WHEN LOANSTATUS IN ('SIGNED', 'PAID') THEN 1 ELSE 0 END
  5.           ) AS qtd_reservado
  6.           ,SUM(
  7.             CASE WHEN LOANSTATUS IN ('PAID') THEN 1 ELSE 0 END
  8.           ) AS qtd_aprovado
  9.           ,COALESCE(SUM(
  10.             CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE') THEN vlr_contrato ELSE 0 END
  11.           ),0) AS vlr_iniciado
  12.           ,SUM(
  13.             CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE')
  14.                  AND LOANSTATUS IN ('SIGNED', 'PAID')
  15.                       THEN vlr_contrato ELSE 0 END
  16.           ) AS vlr_reservado
  17. FROM (
  18.      SELECT CASE
  19.              WHEN L.LOANTYPE = 'NEW' THEN 'NOVO'
  20.              WHEN L.LOANTYPE = 'REFIN' THEN 'REFIN'
  21.              WHEN L.LOANTYPE = 'PORTABILITY' THEN 'PORTABILIDADE'
  22.             END AS TIPO_OPERACAO,
  23.             COALESCE (L.NETVALUE , 0) AS vlr_contrato,
  24.             L.VALUEFORDEPOSIT AS vlr_troco,
  25.             L.LOANSTATUS
  26.       FROM CONTRACTLOAN L
  27.             LEFT JOIN CONTRACT CONTRATO ON CONTRATO.ID = L.CONTRACTID
  28.             LEFT JOIN PERSON P ON P.ID = PERSONID
  29.       WHERE 1 = 1
  30.         AND ($SYS_AGREEMENT$ = -1 OR P.AGREEMENTID = $SYS_AGREEMENT$)
  31.         AND ((CASE $AGREEMENT$ WHEN -1 THEN 1 ELSE $AGREEMENT$ END) = P.AGREEMENTID OR $AGREEMENT$ = -1)
  32.         AND L.LOANSTATUS NOT IN ('CANCELLED', 'CANCELLED_BY_SYSTEM')
  33.         AND TRUNC(CONTRATO.CONTRACTDATE) BETWEEN $STARTDATE$ AND $ENDDATE$
  34.  
  35. )
  36. GROUP BY TIPO_OPERACAO
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