Queries Persona Score

-- ======================================================= QUERYS DE PERSONA MARCIO ===============================================================

-- QUERY 10 PESSOAS INSS COM CONTRATOS DE PORTABILIDADE ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'PORTABILITY' AND AGREEMENTID = 42 AND p.INCOME IS NOT NULL
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS INSS COM CONTRATOS NOVOS ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'NEW' AND AGREEMENTID = 42 AND p.INCOME IS NOT NULL
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS INSS COM CONTRATOS REFIN ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'REFIN' AND AGREEMENTID = 42 AND p.INCOME IS NOT NULL
FETCH FIRST 10 ROWS ONLY;


-- ===============================================
-- QUERY SÓ SIMULOU MAS NAO CONTRATOU  - NAO E POSSIVEL FILTRAR POR PRODUTO
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 42 AND p.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 42 AND p.INCOME IS NOT NULL
FETCH FIRST 10 ROWS ONLY;


-- QUERY PESSOAS QUE SO CADASTRARAM E NAO FIZERAM NADA
WITH cadast AS (
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
WHERE p.AGREEMENTID = 42 AND p.REGISTRATIONSTATUS IN ('COMPLETE', 'APPROVED', 'BACKOFFICE_PENDING', 'REFUSED', 'DOCUMENTSPICTURES_BACK')
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 42
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda,p.AGREEMENTPASSWORD,I.RESULTTYPE
FROM TUDOPROD.PERSON p
LEFT JOIN TUDOPROD.INTEGRATIONMEUINSSQUERY I ON I.PERSONDOCUMENT = P.DOCUMENT
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE P.AGREEMENTID = 42) SELECT DISTINCT * FROM cadast  WHERE ESTADO IS NOT NULL AND RENDA IS NOT NULL AND WORKNAME IS NOT NULL FETCH FIRST 10 ROWS ONLY;




-- =================================== SIAPE ====================================


-- QUERY 10 PESSOAS SIAPE COM CONTRATOS DE PORTABILIDADE ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'PORTABILITY' AND AGREEMENTID = 5
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS SIAPE COM CONTRATOS DE NOVO ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda, cl.NETVALUE AS valor_averbado,
cl.INSTALLMENTVALUE AS valor_parcela, cl.NUMBEROFINSTALLMENTS AS qtd_parcelas, cl.BANKNUMBER AS numero_do_banco
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'NEW' AND AGREEMENTID = 5
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS SIAPE COM CONTRATOS DE REFIN ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda, cl.NETVALUE AS valor_averbado,
cl.INSTALLMENTVALUE AS valor_parcela, cl.NUMBEROFINSTALLMENTS AS qtd_parcelas, cl.BANKNUMBER AS numero_do_banco
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'REFIN' AND AGREEMENTID = 5
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- ===============================================
-- QUERY SÓ SIMULOU MAS NAO CONTRATOU SIAPE - NAO E POSSIVEL FILTRAR POR PRODUTO
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 5
ORDER BY S.SIMULATIONDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- QUERY PESSOAS DO SIAPE QUE SO CADASTRARAM E NAO FIZERAM NADA
WITH cadast AS (
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
WHERE p.AGREEMENTID = 5 AND p.REGISTRATIONSTATUS IN ('COMPLETE', 'APPROVED', 'BACKOFFICE_PENDING', 'REFUSED', 'DOCUMENTSPICTURES_BACK')
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 5
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 5) SELECT DISTINCT * FROM cadast FETCH FIRST 10 ROWS ONLY;

-- ============================================ GOVERNO DE SANTA CATARINA ============================================

-- QUERY 10 PESSOAS DO GOVERNO DE SANTA CATARINA COM CONTRATOS DE PORTABILIDADE ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'PORTABILITY' AND AGREEMENTID = 1
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS GOVERNO SC COM CONTRATOS DE NOVO ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'NEW' AND AGREEMENTID = 1 AND P.INCOME IS NOT NULL
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS GOVERNO SC COM CONTRATOS DE REFIN ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'REFIN' AND AGREEMENTID = 1
FETCH FIRST 10 ROWS ONLY;

-- ===============================================
-- QUERY SÓ SIMULOU MAS NAO CONTRATOU GOVERNO SC - NAO E POSSIVEL FILTRAR POR PRODUTO
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 1 AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 1
FETCH FIRST 10 ROWS ONLY;

-- QUERY PESSOAS DO GOVERNO DE SC QUE SO CADASTRARAM E NAO FIZERAM NADA
WITH cadast AS (
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
WHERE p.AGREEMENTID = 1 AND p.REGISTRATIONSTATUS IN ('COMPLETE', 'APPROVED', 'BACKOFFICE_PENDING', 'REFUSED', 'DOCUMENTSPICTURES_BACK') AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 1 AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 1 AND P.INCOME IS NOT NULL) SELECT DISTINCT * FROM cadast FETCH FIRST 10 ROWS ONLY;


-- ========================================================== GOVERNO DA BAHIA ==================================

-- QUERY 10 PESSOAS DO GOVERNO DA BAHIA COM CONTRATOS DE PORTABILIDADE ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'PORTABILITY' AND AGREEMENTID = 3
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS GOVERNO BA COM CONTRATOS DE NOVO ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'NEW' AND AGREEMENTID = 3
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- QUERY 10 PESSOAS GOVERNO BA COM CONTRATOS DE REFIN ASSINADOS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda, cl.NETVALUE AS valor_averbado,
cl.INSTALLMENTVALUE AS valor_parcela, cl.NUMBEROFINSTALLMENTS AS qtd_parcelas, cl.BANKNUMBER AS numero_do_banco
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND cl.LOANTYPE = 'REFIN' AND AGREEMENTID = 3
ORDER BY C.CONTRACTDATE DESC
FETCH FIRST 10 ROWS ONLY;

-- ===============================================
-- QUERY SÓ SIMULOU MAS NAO CONTRATOU GOVERNO BA - NAO E POSSIVEL FILTRAR POR PRODUTO
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 3 AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 3
FETCH FIRST 10 ROWS ONLY;

-- QUERY PESSOAS DO GOVERNO DE BA QUE SO CADASTRARAM E NAO FIZERAM NADA
WITH cadast AS (
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
WHERE p.AGREEMENTID = 3 AND p.REGISTRATIONSTATUS IN ('COMPLETE', 'APPROVED', 'BACKOFFICE_PENDING', 'REFUSED', 'DOCUMENTSPICTURES_BACK') AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACT c ON c.PERSONID = p.ID
INNER JOIN TUDOPROD.CONTRACTLOAN cl ON cl.CONTRACTID = c.ID
WHERE cl.LOANSTATUS IN ('SIGNED','CCBSIGNED') AND AGREEMENTID = 3 AND P.INCOME IS NOT NULL
MINUS
SELECT DISTINCT p.ID, p.ENROLLMENT AS matricula, p.DOCUMENT AS cpf, p.NAME AS nome,p.BIRTHDATE AS data_nascimento, p.STATE AS estado, p.CITY AS cidade,
p.WORKNAME,p.INCOME AS renda
FROM TUDOPROD.PERSON p
INNER JOIN TUDOPROD.CONTRACTSIMULATION S ON S.PERSONID = p.ID
WHERE S.BECOMECONTRACT =0 AND P.AGREEMENTID = 3 AND P.INCOME IS NOT NULL) SELECT DISTINCT * FROM cadast FETCH FIRST 10 ROWS ONLY;







-- ======================================================= QUERYS DE PERSONA MARCIO POSTGRES ===============================================================

-- 10 PESSOAS COM CONTRATO DE PORTABILIDADE COM CONVENIO SIAPE
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contrato_meutudo c ON c.personid = p.id_person
INNER JOIN public.tb_contractloan_meutudo cl ON cl.contractid = c.id
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE cl.loanstatus IN ('SIGNED','CCBSIGNED') AND cl.loantype = 'PORTABILITY' AND p.agreement_id = 5 LIMIT 10;

-- 10 PESSOAS COM CONTRATO DE NOVO COM CONVENIO SIAPE
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contrato_meutudo c ON c.personid = p.id_person
INNER JOIN public.tb_contractloan_meutudo cl ON cl.contractid = c.id
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE cl.loanstatus IN ('SIGNED','CCBSIGNED') AND cl.loantype = 'NEW' AND p.agreement_id = 5 LIMIT 10;

-- 10 PESSOAS COM CONTRATO DE REFIN COM CONVENIO SIAPE
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contrato_meutudo c ON c.personid = p.id_person
INNER JOIN public.tb_contractloan_meutudo cl ON cl.contractid = c.id
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE cl.loanstatus IN ('SIGNED','CCBSIGNED') AND cl.loantype = 'REFIN' AND p.agreement_id = 5 LIMIT 10;

-- SÓ SIMULOU MAS NAO CONTRATOU SIAPE
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contractsimulation_meutudo s ON s.personid = p.id_person
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE s.becomecontract = 0  AND p.agreement_id = 5 AND p.estado IS NOT NULL
EXCEPT
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contrato_meutudo c ON c.personid = p.id_person
INNER JOIN public.tb_contractloan_meutudo cl ON cl.contractid = c.id
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE cl.loanstatus IN ('SIGNED','CCBSIGNED') AND p.agreement_id = 5 LIMIT 10;

-- SÓ CADASTROU E NAO FEZ MAIS NADA
WITH RESULT AS (
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE p.status_registro IN ('COMPLETE', 'APPROVED', 'BACKOFFICE_PENDING', 'REFUSED', 'DOCUMENTSPICTURES_BACK') AND p.estado IS NOT NULL

EXCEPT
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contrato_meutudo c ON c.personid = p.id_person
INNER JOIN public.tb_contractloan_meutudo cl ON cl.contractid = c.id
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE cl.loanstatus IN ('SIGNED','CCBSIGNED') AND p.agreement_id = 5 AND p.estado IS NOT NULL

EXCEPT
SELECT DISTINCT p.matricula, p.num_cpf AS cpf, p.nome,p.data_nascimento, p.estado, p.cidade, p.work_nome, cch.base_calculo AS renda
FROM public.tb_pessoa_meutudo p
INNER JOIN public.tb_contractsimulation_meutudo s ON s.personid = p.id_person
INNER JOIN meutudo.siape_cch_cliente cch ON cch.cpf = p.num_cpf AND cch.matricula = p.matricula
WHERE p.agreement_id = 5 AND p.estado IS NOT NULL) SELECT * FROM RESULT WHERE renda IS NOT NULL LIMIT 10;