Advertisement
pedravirada

Untitled

Jun 11th, 2019
992
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
Advertisement
RAW Paste Data Copied
Advertisement