Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT TIPO_OPERACAO,
- CASE WHEN COUNT(1) > 0 THEN COUNT(1) ELSE 0 END AS qtd_iniciado
- ,SUM(
- CASE WHEN LOANSTATUS IN ('SIGNED', 'PAID') THEN 1 ELSE 0 END
- ) AS qtd_reservado
- ,SUM(
- CASE WHEN LOANSTATUS IN ('PAID') THEN 1 ELSE 0 END
- ) AS qtd_aprovado
- ,COALESCE(SUM(
- CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE') THEN vlr_contrato ELSE 0 END
- ),0) AS vlr_iniciado
- ,SUM(
- CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE')
- AND LOANSTATUS IN ('SIGNED', 'PAID')
- THEN vlr_contrato ELSE 0 END
- ) AS vlr_reservado
- FROM (
- SELECT CASE
- WHEN L.LOANTYPE = 'NEW' THEN 'NOVO'
- WHEN L.LOANTYPE = 'REFIN' THEN 'REFIN'
- WHEN L.LOANTYPE = 'PORTABILITY' THEN 'PORTABILIDADE'
- END AS TIPO_OPERACAO,
- COALESCE (L.NETVALUE , 0) AS vlr_contrato,
- L.VALUEFORDEPOSIT AS vlr_troco,
- L.LOANSTATUS
- FROM CONTRACTLOAN L
- LEFT JOIN CONTRACT CONTRATO ON CONTRATO.ID = L.CONTRACTID
- LEFT JOIN PERSON P ON P.ID = PERSONID
- WHERE 1 = 1
- AND ($SYS_AGREEMENT$ = -1 OR P.AGREEMENTID = $SYS_AGREEMENT$)
- AND ((CASE $AGREEMENT$ WHEN -1 THEN 1 ELSE $AGREEMENT$ END) = P.AGREEMENTID OR $AGREEMENT$ = -1)
- AND L.LOANSTATUS NOT IN ('CANCELLED', 'CANCELLED_BY_SYSTEM')
- AND TRUNC(CONTRATO.CONTRACTDATE) BETWEEN $STARTDATE$ AND $ENDDATE$
- )
- GROUP BY TIPO_OPERACAO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement