none
CTE com recursividade RRS feed

  • Pergunta

  • Ola, pessoal

    preciso de uma ajudinha com CTE com recursividade

    só vi e utilizei em exemplos mais simples. Neste não estou avançando (e tbm, não sei se o melhor caminho é por sql, se alguem quiser dar alguma dica também seria bom)


    Tenho uma tabela de Titulos, onde cada titulo pode ter um ou mais titulos de origem, gerados a partir de uma renegociação.

    Podem ser feitas renegociações de títulos já renegociados, indefinidas vezes, assim , para chegar a um título "original" (que foi gerado por uma venda, por exemplo) há necessidade de cascatear os títulos até chegar no titulo original.


    Precisaria retornar todos os títulos de origem de um título, recursivamente, até chegar no título original.

    A estrutura que retornaria seria esta tabela:
    CodTitulo - ValorTitulo - CodTituloOrigem - ValorTituloOrigem

    with CTE_TituloOrigem as (
    	Select  
    		TitulosDestino.CodFinanceiroTitulos as CodTitulo, 		
    		TitulosDestino.Valor as ValorTitulo,
                    TitulosOrigem.CodFinanceiroTitulos as CodTituloOrigem,
    		TitulosOrigem.Valor as ValorTituloOrigem,
    
    	From 
    	(
    		Select  
    			titRen.CodFinanceiroTitulos as CodFinanceiroTitulos, tit.Valor
    		From 
    			TAB_FinanceiroTitulosRenegociacao ren
    			Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRen 
    				on ren.Cod = titRen.CodFinanceiroTitulosRenegociacao and titRen.Tipo = 'D'
    			Inner Join TAB_FinanceiroTitulos tit on tit.Cod = titRen.CodFinanceiroTitulos
    	) TitulosDestino, 
    	(
    		Select  
    			titRen.CodFinanceiroTitulos as CodFinanceiroTitulos, tit.Valor
    		From 
    			TAB_FinanceiroTitulosRenegociacao ren
    			Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRen 
    				on ren.Cod = titRen.CodFinanceiroTitulosRenegociacao and titRen.Tipo = 'O'
    			Inner Join TAB_FinanceiroTitulos tit on tit.Cod = titRen.CodFinanceiroTitulos
    	) TitulosOrigem
    
    )


    A tabela TAB_FinanceiroTitulosRenegociacao é N-N , com o codigo do titulo de origem e destino.

    Assim está funcionando, sem a recursividade (retorna apenas o primeiro nível)


    Julio C.


    • Editado Julio Costi sábado, 23 de novembro de 2019 14:17
    sábado, 23 de novembro de 2019 14:01

Todas as Respostas

  • Podem ser feitas renegociações de títulos já renegociados, indefinidas vezes, assim , para chegar a um título "original" (que foi gerado por uma venda, por exemplo) há necessidade de cascatear os títulos até chegar no titulo original.



    Julio,

    Olá, vamos por partes, no trecho acima você cita que podem existir indefinidas renegociações, por padrão uma CTE recursiva estabelece trabalha com até 100 níveis de recursividade, isso pode ser alterado, mas acredito que você deverá definir sim um valor de limite.

    Você tem ideia de qual poderia ser este valor?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 23 de novembro de 2019 15:20
  • Boa tarde,

    Julio, qual(is) coluna(s) é(são) utilizada(s) para relacionar o título de origem com o título de destino?

    Não se sei entendi errado mas como não vi a clausula Where no trecho que você postou, acredito que a query que você postou vai retornar o produto cartesiano de todos os títulos de origem com todos os títulos de destino.


    Assinatura: http://www.imoveisemexposicao.com.br

    sábado, 23 de novembro de 2019 16:12
  • Deleted
    sábado, 23 de novembro de 2019 19:38
  • Ola, pessoal

    "trabalha com até 100 níveis de recursividade"
    Sim, 
    estou fazendo uma análise previa para prever a situação de renegociação de renegociação, para verificar a viabilidade de fazer assim, ou se terá que ser por outra forma.
    Tenho conhecimento deste limite, vai ser definido um limite com a análise de negócio. mas um número bem menor do que 100.

    -----

    "Não se sei entendi errado mas como não vi a clausula Where no trecho que você postou"
    Estou revisando o SQL que fiz, e nas modificações para testes, acabei deixando errado, realmente. Não está com o vínculo entre as tabelas. Na sequência, vou postar o código correto.

    -----

    José,

    Quando um título é renegociado, este título é baixado sim. 

    Para tentar elucidar melhor a situação, vou colocar cada SQL e o respectivo resultado, acho que vai esclarecer melhor a estrutura.

    (claro que há mais colunas, mas que acho que não são relevantes para este caso)

    Select Cod, Valor, ValorPago from TAB_FinanceiroTitulos

    Títulos

    Select Cod as CodRenegociacao, Situacao, FormaPagamento from TAB_FinanceiroTitulosRenegociacao


    Select Cod, CodFinanceiroTitulosRenegociacao as CodRenegociacao, CodFinanceiroTitulos, Tipo as Tipo_OrigemDestino from TAB_FinanceiroTitulosRenegociacaoTitulos

    Nesta tabela são feitos os vínculos dos títulos com a renegociação.

    Neste caso, a renegociação Cod "2" teve 2 títulos de origem (que foram baixados) e 3 títulos de destino (novos títulos que ficam em aberto).

    Um título pode ser vinculado a uma renegociação como "D" (destino), e posteriormente, no caso de uma "renegociação de renegociação", o mesmo título pode aparece em outra renegociação como "O" (origem)

    (talvez foi um erro ter feito a modelagem , definindo se o título é de origem ou destino em uma coluna.... deveria ter uma tabela separada para os títulos de origem, e outra para o destino, mas enfim)

    Então, eu gostaria de pegar cada CodFinanceiroTitulos desta tabela, onde for DESTINO, e localizar os de origem. Porém, cada título de origem pode já ser destino de uma renegociação. Por isso a necessidade da recursividade.

    Exemplo: caso o título 590999 seja um título originado por uma renegociação (destino), teria que ser pego os títulos de origem dele. e assim sucessivamente.

    A idéia é montar uma estrutura que me retorne todos os títulos de ORIGEM, descendo na hierarquia.

    Poderia fazer de outras formas que não por SQL , mas criar esta estrutura vai facilitar muito a manutenção, pois há diversas utilizações para a estrutura, e não teria que ser replicada (sem falar em desempenho).

    A coluna "Cod" em cada tabela, é um id interno (não visivel para o usuário), serve para facilitar o vínculo entre as tabelas.

    Muito obrigado a todos pelas respostas, desde já, e pela atenção.




    • Editado Julio Costi segunda-feira, 25 de novembro de 2019 19:12
    segunda-feira, 25 de novembro de 2019 19:07
  • Deleted
    • Marcado como Resposta Julio Costi terça-feira, 26 de novembro de 2019 01:50
    • Não Marcado como Resposta Julio Costi quarta-feira, 18 de março de 2020 19:39
    segunda-feira, 25 de novembro de 2019 19:52
  • Deleted
    • Marcado como Resposta Julio Costi terça-feira, 26 de novembro de 2019 01:50
    • Não Marcado como Resposta Julio Costi quarta-feira, 18 de março de 2020 19:39
    segunda-feira, 25 de novembro de 2019 20:11
  • Certo!

    entendi, José, parece que a solução é bem por aí mesmo

    me faltava conhecimento para "startar" a recursividade (o âncora)

    não é exatamente o campo "Baixado", mas captei a lógica, agora clareou.

    muuuito obrigado

    estou analisando e tentando implementar.


    Julio C.

    terça-feira, 26 de novembro de 2019 02:13
  • Deleted
    terça-feira, 26 de novembro de 2019 11:32
  • "A maior dificuldade foi nos nomes das tabelas e colunas; me confundia neles e acabei criando uma versão resumida de cada um, para melhor compreensão do código SQL. "

    Sim, tentando implementar agora, eu tbm tive dificuldade para entender cada um dos campos.

    --

    Certo

    Mas ainda estou com 2 problemas.

    O critério para pegar os títulos de origem seria este


    with TitulosRenegociados as (
    Select  tit.Cod From TAB_FinanceiroTitulos tit
    Inner join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    on titRenO.CodFinanceiroTitulos = tit.Cod and titRenO.Tipo = 'O'
    and not exists (select titRenD.CodFinanceiroTitulosRenegociacao from TAB_FinanceiroTitulosRenegociacaoTitulos titRenD 
    where titRenD.CodFinanceiroTitulos = titRenO.CodFinanceiroTitulos
    and titRenD.Tipo = 'D'
    )
    -- pega títulos de origem em que não estejam como Destino em nenhuma renegociação (nível raiz)
    ),

    (esta seria a Titulo_Aberto, no teu exemplo)


    Porém, estou pensando seriamente em performance. Sei que só testando, mas não ta me parecendo uma boa prática, e não consigo pensar em outra forma de pegar os títulos de origem da raiz.

    --

    Outro problema é que não estou conseguindo fazer funcionar a recursividade.

    Já alterei de várias formas, algo está fugido do meu entendimento

    Poderia dar um auxílio?

    ficou assim no final:

    (estoura erro das 100 recursões)

    with TitulosRenegociados as (
    	Select  tit.* From TAB_FinanceiroTitulos tit
    	Inner join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    		on titRenO.CodFinanceiroTitulos = tit.Cod and titRenO.Tipo = 'O'
    		and not exists (select titRenD.CodFinanceiroTitulosRenegociacao from TAB_FinanceiroTitulosRenegociacaoTitulos titRenD 
    			where titRenD.CodFinanceiroTitulos = titRenO.CodFinanceiroTitulos
    			and titRenD.Tipo = 'D'
    		)			
    		-- pega títulos de origem em que não estejam como Destino em nenhuma renegociação (nível raiz)
    	),
    CTE_TituloOrigem as (
    	Select 
    		1 as Tipo,
    		tr.Cod,
    		1 as Nivel, 	
    		tit.Numero, tit.Sequencia,
    		titRenO.CodFinanceiroTitulos as CodFinanceiroTitulosOrigem,
    		tit.Valor as ValorOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 	
    		titRenD.CodFinanceiroTitulosRenegociacao
    	From
    		TitulosRenegociados tr 
    		Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    			 on titRenO.Tipo = 'O'
    			 and titRenO.CodFinanceiroTitulos = tr.Cod
    		Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRenD
    			 on titRenD.Tipo = 'D' 
    			 and titRenD.CodFinanceiroTitulosRenegociacao = titRenO.CodFinanceiroTitulosRenegociacao
    		Inner Join TAB_FinanceiroTitulos tit on tit.Cod = titRenO.CodFinanceiroTitulos
    
    	Union ALL
    
    	Select 
    		2 as Tipo,
    		CTE_TituloOrigem.Cod,
    		(CTE_TituloOrigem.Nivel + 1) as Nivel, 	
    		tit.Numero, tit.Sequencia,
    		titRenO.CodFinanceiroTitulos as CodFinanceiroTitulosOrigem,
    		tit.Valor as ValorOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 	
    		titRenD.CodFinanceiroTitulosRenegociacao
    	From 
    		CTE_TituloOrigem 
    		Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    			 on titRenO.Tipo = 'O'
    			 and titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.Cod
    		Inner Join TAB_FinanceiroTitulosRenegociacaoTitulos titRenD
    			 on titRenD.Tipo = 'D'
    			 and titRenO.CodFinanceiroTitulosRenegociacao = titRenD.CodFinanceiroTitulosRenegociacao
    		Inner Join TAB_FinanceiroTitulos tit on tit.Cod = titRenO.CodFinanceiroTitulos
    )
    
    Select * from CTE_TituloOrigem


    Julio C.


    • Editado Julio Costi terça-feira, 26 de novembro de 2019 14:04
    terça-feira, 26 de novembro de 2019 14:01
  • Deleted
    terça-feira, 26 de novembro de 2019 14:54
  • Julio,

    Experimente fazer um teste trocando a coluna utilizada no Join da parte recursiva de:

    			 and titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.Cod
    

    para

    			 and titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.CodFinanceiroTitulos
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 26 de novembro de 2019 15:14
  • Julio,

    Experimente fazer um teste trocando a coluna utilizada no Join da parte recursiva de:

    			 and titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.Cod

    para

    			 and titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.CodFinanceiroTitulos

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    Ola

    A princípio, assim funciona. Acho que era esse o detalhe (vinculo do titulo de destino com o de origem da consulta ancora).

    Estou fazendo testes.

    Depois retorno aqui o com o resultado final.

    Obrigado às particiações!! 


    Julio C.

    quarta-feira, 27 de novembro de 2019 14:34
  • Ola

    para a necessidade até então, funcionou perfeitamente!! 
    Era isso mesmo.

    Porém, mais uma dúvida:

    Na consulta recursiva, não consigo fazer uma subquery com SUM() na tabela de renegociações... Precisaria disso para pegar o percentual do valor do título em relação ao valor total da renegociação...


    Mensagem 467, Nível 16, Estado 1, Linha 12
    GROUP BY, HAVING ou funções de agregação não são permitidas na parte recursiva de uma expressão de tabela comum recursiva 'CTE_TituloOrigem'.



    Alguma dica?


    Julio C.

    quinta-feira, 28 de novembro de 2019 03:45
  • Deleted
    quinta-feira, 28 de novembro de 2019 11:25
  • Na consulta recursiva, não consigo fazer uma subquery com SUM() na tabela de renegociações... Precisaria disso para pegar o percentual do valor do título em relação ao valor total da renegociação...

    Júlio, como percebeu pela mensagem de erro não há como utilizar GROUP BY na parte recursiva. Já tive problemas semelhantes e contornei utilizando subconsulta na cláusula SELECT, mas não sei se será de auxílio para seu caso.

    Na tabela TAB_FinanceiroTitulosRenegociacao consta o valor total da renegociação ou esse valor tem que ser calculado? 
    Se tem que ser calculado, deve-se considerar os valores dos títulos novos ou dos títulos antigos?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Este valor precisa ser calculado. Não tem o total da renegociação, consta apenas o valor que foi renegociado do título (no caso dos de Origem) e o valor do título (destinos), gerados a partir da renegociação.

    (Eu já estava tentando  pegar esses valores com um sub-select , em um Select na CTE gerada. Mas ainda sem sucesso).

    Realmente isso é um problema. Conforme imagem:


    Atentando-se para a parte selecionada(em azul), para exemplificar na prática o que ocorre:

    Tem-se os títulos 123, 456 e 789 (que são origem RAIZ, não são gerados por nenhuma renegociação). São eles que o sistema precisa encontrar, para mostrar ao usuário.

    Na renegociação 12 (CodFinanceiroTitulosRenegociacao), esses 3 títulos foram renegociados.

    Titulos de origem: 
    123    100,00   20%
    456    200,00   40%
    789    200,00   40%

    Nesse momento, os títulos de origem foram baixados, e foram geradas 4 novas parcelas (REN-12   de 125,00 cada, porém este valor pode variar) de destino.

    Todos ficam vinculados nesta tabela "TAB_FinanceiroTitulosRenegociacaoTitulos", com a identificação na coluna Tipo, se é 'O' (origem) ou 'D' (destino).

    -

    Porém, posteriormente no meu exemplo, fiz uma NOVA renegociação (13), envolvendo um dos títulos REN-12 (1) gerado (125,00), e o 555 (145,00 - título origem RAIZ) total, 270,00...... Gerando 3 novos títulos REN-13 (1,2,3) de valores  100,00 , 100,00 e 70,00 (total dos títulos de destino = 270,00).

    Ou seja, há a possibilidade de haver títulos de DIVERSOS níveis  em nova renegociação.

    Do ponto de vista do usuário, quando ele faz o pagamento de um título que é uma renegociação (REN-alguma coisa), para diversos fins no sistema , deve considerar como se estivesse pagando os títulos de origem, proporcionalmente. (cálculo de comissões, por exemplo) 

    Precisaria que cada renegociação retornasse os títulos de origem com o % de representatividade deles na renegociação, porém, lembrando que ocorre em cascata, ou seja:

    Titulos renegociados:

    REN-13     125,00    46,3%
    555          145,00    53,7%
    --------------------------------------

    Titulos novos

    Titulo - Valor
    REN-13 1     100,00   (37,03%)
    REN-13 2     100,00   (37,03%)
    REN-13 3       70,00   (25,93%)
    ---------------------
    Total = 270,00

    Está ficando complexo explicar, começa a questão de regras de negócio bem específicas, eu não sei até que ponto vão poder me ajudar com essas regras internas, mas para contextualizar a questão no geral, é isso que coloquei acima.

    Obrigaaaado!

    



    • Editado Julio Costi quinta-feira, 28 de novembro de 2019 12:36
    quinta-feira, 28 de novembro de 2019 12:06
  • Se for o caso... Há possibilidade de alterar a modelagem... se não tiver outra forma.

    Adicionar colunas para colocar os percentuais de cada título no momento em que são feitas as renegociações...  para facilitar essa parte.

    Mas se tiver como pegar os % apenas via código, em cascata, melhor.

    (observo isso pois talvez haja sugestão de melhor a modelagem ou alguma prática que foi foi utilizada está dificultando muito o suprimento desta necessidade)


    Julio C.

    quinta-feira, 28 de novembro de 2019 12:30
  • Julio, talvez uma alternativa seja utilizar uma CTE depois da CTE recursiva para pegar as linhas da CTE recursiva e fazer a soma desejada.

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Sugerido como Resposta Junior Galvão - MVPMVP quinta-feira, 28 de novembro de 2019 21:42
    • Marcado como Resposta Julio Costi sexta-feira, 6 de março de 2020 13:53
    • Não Marcado como Resposta Julio Costi quarta-feira, 18 de março de 2020 19:39
    quinta-feira, 28 de novembro de 2019 12:50
  • with TitulosRenegociados as (
    	-- pega títulos de origem em que não estejam como Destino em nenhuma renegociação (nível raiz)
    	Select tit.Cod, tit.CodFinanceiroTitulosRenegociacao From TAB_FinanceiroTitulos tit
    	Inner join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    		on titRenO.CodFinanceiroTitulos = tit.Cod and titRenO.Tipo = 'O'
    		and not exists (select titRenD.CodFinanceiroTitulosRenegociacao from TAB_FinanceiroTitulosRenegociacaoTitulos titRenD 
    			where titRenD.CodFinanceiroTitulos = titRenO.CodFinanceiroTitulos
    			and titRenD.Tipo =  'D'
    		)
    ),
    -- Facilita as consultas às tabelas que estão dos títulos renegociados (Origem e Destino), alterando a modelagem
    CTE_TitulosOrigemRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    ),
    CTE_TitulosDestinoRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'D'
    ),
    CTE_TitulosTotalOrigemRenegociacao as (
    	-- Pega o valor total da renegociação de origem referente apenas ao título de destino
    	---- Problema: não encontra o título de origem entre os títulos, caso tenha mais de 1 renegociação entre eles
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, sum(ren.Valor) as Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren 
    	inner join TAB_FinanceiroTitulosRenegociacaoTitulos ren2 
    		on ren2.CodFinanceiroTitulos = ren.CodFinanceiroTitulos
    	where ren2.Tipo = 'O' and ren.tipo = 'D' 
    	group by ren.CodFinanceiroTitulosRenegociacao
    	/*  -- versão anterior 
    	---- Problema: Pegava apenas o valor total da renegociação de origem, sem considerar os títulos envolvidos na nova renegociação
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, 
    		sum(ren.Valor) as Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    	group by ren.CodFinanceiroTitulosRenegociacao
    	*/
    ),
    CTE_Pagamentos as (
    	select pg.CodFinanceiroTitulos, sum(pg.Valor) as Valor from TAB_FinanceiroTitulosPagamentos pg 
    	where isnull(pg.BaixaRenegociacao,0) = 0
    	group by pg.CodFinanceiroTitulos
    ),
    CTE_TituloOrigem as (
    	-- Consulta âncora para a recursividade
    	Select 
    		tr.Cod,
    		1 as Nivel, 
    		cast (concat (tr.Cod, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		titRenO.CodFinanceiroTitulos as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 	
    		tr.CodFinanceiroTitulosRenegociacao ,
    		titRenO.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,		
    		titRenD.Valor as ValorTituloDestino,
    		titRenO.Valor as ValorRenegociacaoTituloOrigem,
    
    		-- na âncora não precisa linkar com o registro de origem (o valor do título de origem será o mesmo)
    		titRenO.Valor as ValorTituloOrigem,		
    
    		totRen.Valor as ValorTotal,
    		convert(money, 100) as Proporcao
    
    	From
    		TitulosRenegociados tr 
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = tr.Cod
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenD.CodFinanceiroTitulosRenegociacao = titRenO.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen 
    			 on totRen.CodFinanceiroTitulosRenegociacao = tr.CodFinanceiroTitulosRenegociacao
    
    	Union ALL
    
    	-- Consulta recursiva
    	Select 
    		CTE_TituloOrigem.Cod,
    		(CTE_TituloOrigem.Nivel + 1) as Nivel, 	
    		cast (concat (CTE_TituloOrigem.Caminho, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		CTE_TituloOrigem.CodFinanceiroTitulosOrigem as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 
    		titreno.CodFinanceiroTitulosRenegociacao,
    		titRenD.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,
    		titRenD.Valor as ValorTituloDestino,
    		titRenO.Valor as ValorRenegociacaoTituloOrigem, 
    		titOrigem.Valor	as ValorTituloOrigem,
    		totRen.Valor as ValorTotal,
    		(titOrigem.Valor * 100 / totRen.Valor) 	as Proporcao
    
    	From 
    		CTE_TituloOrigem
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.CodFinanceiroTitulos
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenO.CodFinanceiroTitulosRenegociacao = titRenD.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosOrigemRenegociacao titOrigem 
    			on titOrigem.CodFinanceiroTitulos = CTE_TituloOrigem.Cod
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen on totRen.CodFinanceiroTitulosRenegociacao = CTE_TituloOrigem.CodFinanceiroTitulosRenegociacao		
    )
    
    , CTE_Titulos as (
    	Select 
    		-- valor do título de destino da renegociação
    		cte.ValorTituloDestino,
    		
    		-- valor total da renegociação
    		(select sum(ren.Valor) from TAB_FinanceiroTitulosRenegociacaoTitulos ren 
    		where ren.CodFinanceiroTitulosRenegociacao = cte.CodFinanceiroTitulosRenegociacao and ren.Tipo = 'O')
    			as ValorTotalRenegociacao,
    
    		-- Proporção do título na renegociação de nivel mais baixo
    		cte.ValorRenegociacaoTituloOrigem * 100  / 
    		(select sum(ren.Valor) from TAB_FinanceiroTitulosRenegociacaoTitulos ren 
    				where ren.CodFinanceiroTitulosRenegociacao = cte.CodFinanceiroTitulosRenegociacao and ren.Tipo = 'O')
    		 * cte.Proporcao / 100 
    			as Proporcao,
    
    		cte.Caminho,
    		cte.Nivel, 
    		cte.CodFinanceiroTitulos,
    		tit.Numero, 
    		tit.Sequencia as Seq,
    		cte.CodFinanceiroTitulosOrigem,
    		titO.Numero as NumeroTituloOrigem,
    		-- renegociação de origem
    		titO.CodFinanceiroTitulosRenegociacao,
    		-- renegociação em questão
    		cte.CodFinanceiroTitulosRenegociacaoDestino,
    		-- valor total da renegociacao origem (CodFinanceiroTitulosRenegociacao)
    		cte.valortotal as ValorTotalRenegociacaoOrigem,
    		-- valor dos titulos de origem da renegociaçao até o primeiro nivel de recursividade
    		cte.ValorTituloOrigem, 
    		-- valor do título de origem da renegociação até o ultimo nivel de recursividade
    		cte.ValorRenegociacaoTituloOrigem,		
    
    		tit.DataEmissao,
    		titO.DataEmissao as DataTituloOrigem
    	from CTE_TituloOrigem cte 
    	inner Join TAB_FinanceiroTitulos tit on tit.Cod = cte.CodFinanceiroTitulos
    	inner Join TAB_FinanceiroTitulos titO on titO.Cod = cte.CodFinanceiroTitulosOrigem
    )
    
    select  tit.*	
    from CTE_Titulos tit 
    order by CodFinanceiroTitulos

    Ola, pessoal

    por um período não continuei trabalhando nesta demanda, mas agora estou em vias de finalizar este trabalho. Ficou muito interessante a consulta.

    Gostaria primeiramente de compartilhar ela aqui, exatamente como está no momento.

    E solicitar auxílio, se for possível (pois eu sei que é um assunto que ficou bem complexo), em um detalhe que não estou conseguindo resolver (ainda).



    Relembrando a situação:

    Cada renegociação é feita para determinados títulos de origem (contas geradas por nota fiscal), gerando um valor total, que pode ser parcelado em várias vezes
    Criam-se novos títulos (com uma nomenclatura "REN") para cada parcela.

    Exemplo:

    cliente deve títulos 1234, 555 e 790, totalizando 5.000,00

    Por meio das renegociações, cria-se novos títulos, por exemplo usua´rio vai quitar o montando em 4x
    REN-1 1  2.000
    REN-1 2  1.000
    REN-1 3  1.000
    REN-1 4  1.000

    (os valores dos títulos de origem e destino são totalmente independentes entre si)

    --

    Exemplo de um caso de uso:

    É feita uma renegociação, que gera um título de "destino da renegociação" = REN-14  (apenas uma parcela)

    -----------------------------------------

    Renegociação REN-14

    Títulos de origem
    2506 = 20.000,00  75,19%
    5107 = 6.600,00   24,81%
    total = 26.600,00   100%

    Titulo de destino gerado:
    REN-14-1 = 26.600,00

    -----------------------------------------

    (ambos de origem - NF)

    No sistema, há necessidade de "voltar" ao título de origem, por exemplo, para o cálculo de comissões. No relatório de comissões, quando for paga a REN-14 serão mostrados os títulos 2506 e 5107, proporcionalmente. Isso está funcionando, atualmente.

    ---

    A nova demanda, que está sendo buscada solução, é fazer renegociação de títulos já renegociados, ou seja, para recuperar os títulos de origem (e valores proporcionais) teremos que percorrer as renegociações em cascata, considerando que em cada renegociação tem proporções diferentes.


    Continuando no exemplo prático:

    É feita uma nova renegociação, REN-15 a partir do título gerado REN-14
    (na prática, o cliente não conseguiu pagar a REN-14 e vai renegociar para frente, com juros. Baixa o título que gerou ela, e a nova renegociação fica pendente)
    a REN-15 inclui a REN-14 apenas, com o mesmo valor.

    -----------------------------------------

    Renegociação REN-15

    Titulo de origem:
    REN-14-1 = 26.600,00  100%
    total    = 26.600,00  100%

    Titulo de destino gerado
    REN-15-1 = 26.600,00

    -----------------------------------------



    No título REN-15-1 é feito um pagamento de 15.000, portanto baixou o saldo.
        ficando 26.600,00 - 15.000,00 = 11.600,00

    Então é feita uma nova renegociação, REN-18, incluindo o saldo da REN-15 = 11.600,00 + o título 435100 (origem - NF) 8.000, totalizando 19.600,00.

    -----------------------------------------

    Renegociação REN-18

    Titulos de origem:
    REN-15-1     11.600,00   59,18%
    435100 (NF)   8.000,00  40,82%
    total      = 19.600,00    100%

    Titulo de destino gerado:
    REN-18-1    19.600,00


    -----------------------------------------

    Renegociação REN-19

    Titulos de origem:
    1212   (NF)    100,00  40%
    123123 (NF)    150,00  60%
    total      =   250,00 100%

    Titulo de destino gerado:
    REN-19-1    50,00
    REN-19-2    250,00

    -----------------------------------------

    Feito pagamento parcial do título  REN-19-1 , de 35,00, restando saldo de 15,00
    Feito pagamento parcial do título  REN-19-2 , de 199,00, restando saldo de 1,00

    -----------------------------------------

    Renegociação REN-22

    Titulos de origem:
    44321  (NF) 10,00 38,46%
    REN-19-1    15,00 57,69%
    REN-19-2     1,00  3,85%
    Total       26,00   100%


    Titulo de destino gerado:
    REN-22-1    20,00  
    REN-22-2     6,00

    -----------------------------------------


    Até aí , tudo certo. executando a consulta, tudo funciona.
    Os percentuais de representatividade vão "descendo" e vão se aplicando um sob o outro, conforme os % em cada renegociação.

    Por exemplo, na renegociação 22, o título REN-19-1 é 57,69%
    mas ele proprio é outra renegociação (19), que envolve os títulos   1212 (40%)   e   123123  (60%)

    Então, os 57,69% referente ao % de representatividade do título dentro da REN-22 , são convertidos em 34,62%  e  23,08%, respectivamente (como é a proporção deles estão na REN-19, onde é o mais baixo nível - títulos de origem em NF)



    O problema é o seguinte:

    -----------------------------------------

    Renegociação REN-23

    Titulos de origem:
    REN-22-2     6,00 100%
    Total        6,00 100%

    Titulo de destino gerado:
    REN-23-1     6,00

    -----------------------------------------


    Reparem que, desta vez, foi feita uma renegociação pegando apenas 1 dos títulos de renegociação anterior (22) como origem.
    Nos outros casos, todos haviam incluido todos os títulos de uma determinada renegociação como origem (ou seja, sempre 100% do valor da renegociação).

    Nessa situação, com a solução proposta, a proporção não fica correta.

    Isso ocorre devido ao valor proporcional considerar o valor total da renegociação de origem.
    Deveria ser considerado apenas o valor da renegociação de origem "referente aos títulos incluídos na nova renegociação".

    Exemplo: na REN-23, está me retornando "valor da renegociação de origem" = 26,00 (total da REN 22)
    Porém, apenas um título de 6,00 foi adicionado na renegociação nova. Este valor que deveria retornar, e estou com extrema dificuldade para conseguir pegar este valor corretamente.

    Teria que pegar o valor do título na renegociação de origem, onde o "titulo de destino" for igual ao "titulo de origem" entre as renegociações.

    Isso consegui fazer, na CTE_TitulosTotalOrigemRenegociacao

    Porém, quando há um intervalo de renegociações entre a renegociação final e o título de origem, não funciona (não encontra os títulos).
    A tentativa que estou fazendo agora é fazer uma nova recursividade, dentro desta CTE_TitulosTotalOrigemRenegociacao, para chegar à proporção do título da renegociação onde o título foi gerado. Estou tendo grande dificuldade, na parte lógica desta solução.


    Dentro deste conexto, alguem poderia dar alguma idéia, se eu conseguiria obter este valor com recursividade, ou se a consulta está ficando complexa demais (até para questões de manutenção), e poderia ser utilizado uma function ou procedure, fazendo um LOOP??



    • Editado Julio Costi quinta-feira, 19 de março de 2020 22:51 correção em um %
    sexta-feira, 6 de março de 2020 14:39
  • Pessoal,

    venho trazer uma notícia que:

    Depois de muito bater cabeça (entre indas e vindas a esta tarefa, em meio a outras tarefas que tive que resolver durante este tempo), com a IMENSA ajuda de vocês, e conversando bastante com a minha equipe na empresa, conseguimos chegar a uma conclusão.

    Analisando, precisava de algumas alterações (links entre as tabelas de Origem e Destino, cálculo da proporção e no final conversões para decimal por causa dos arredondamentos) na CTE recursiva.

    -

    As Renegociações de Títulos tem particularidades nas regras de negócio que tornam extremamente complexa. Podem variar muito.. Por exemplo, pode ter títulos de diversos níveis dentro de uma mesma renegociação (isso é, renegociação DE renegociação.....), também podem haver pagamentos feitos anteriores a uma nova renegociação nos títulos originados por ela, tem a necessidade que um título  DE ORIGEM poder ou não ter NF vinculado (e se não tiver NF o cenário muda, não tem pagamento de comissão por exempl), e vários, vários vários, outros detalhes que tornaram a tarefa ultra-complexa (para o meu nível de conhecimento atual).

    Foi fantástico e imensamente gratificante quando todos os numerozinhos e proporções começaram a fechar, independentemente do nível de cada título da renegociação


    Resolvi da seguinte forma (e desde já me proponho a auxiliar no que eu puder quem tiver demandas semelhantes a esta):


    with TitulosRenegociados as (
    	-- pega títulos de origem em que não estejam como Destino em nenhuma renegociação (nível raiz)
    	Select tit.Cod, tit.CodFinanceiroTitulosRenegociacao From TAB_FinanceiroTitulos tit
    	Inner join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    		on titRenO.CodFinanceiroTitulos = tit.Cod and titRenO.Tipo = 'O'
    		and not exists (select titRenD.CodFinanceiroTitulosRenegociacao from TAB_FinanceiroTitulosRenegociacaoTitulos titRenD 
    			where titRenD.CodFinanceiroTitulos = titRenO.CodFinanceiroTitulos
    			and titRenD.Tipo =  'D'
    		)
    ),
    -- Facilita as consultas às tabelas que estão dos títulos renegociados (Origem e Destino), alterando a modelagem
    CTE_TitulosOrigemRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    ),
    CTE_TitulosDestinoRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'D'
    ),
    CTE_TitulosTotalOrigemRenegociacao as (
    	-- valor da renegociação considerando apenas títulos da renegociação de destino	
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, 
    		sum(ren.Valor) as Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    	group by ren.CodFinanceiroTitulosRenegociacao
    ),
    CTE_Pagamentos as (
    	select pg.CodFinanceiroTitulos, sum(pg.Valor) as Valor from TAB_FinanceiroTitulosPagamentos pg 
    	where isnull(pg.BaixaRenegociacao,0) = 0
    	group by pg.CodFinanceiroTitulos
    ),
    CTE_TituloOrigem as (
    	-- Consulta âncora para a recursividade
    	Select 
    		tr.Cod,
    		1 as Nivel, 
    		cast (concat (tr.Cod, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		titRenO.CodFinanceiroTitulos as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 	
    		tr.CodFinanceiroTitulosRenegociacao ,
    		titRenO.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,		
    		convert(decimal(20,8), titRenD.Valor) as ValorTituloDestino,
    		convert(decimal(20,8), titRenO.Valor) as ValorRenegociacaoTituloOrigem,
    
    		-- na âncora não precisa linkar com o registro de origem (o valor do título de origem será o mesmo)
    		convert(decimal(20,8),titRenO.Valor) as ValorTituloOrigem,
    
    		convert(decimal(20,8),totRen.Valor) as ValorTotal,
    		convert(decimal(20,8),totRen2.Valor) as ValorTotalRenegociacaoDestino,
    		convert(decimal(20,8), 100) as Proporcao
    	From
    		TitulosRenegociados tr 
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = tr.Cod
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenD.CodFinanceiroTitulosRenegociacao = titRenO.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen 
    			 on totRen.CodFinanceiroTitulosRenegociacao = tr.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen2 
    			on totRen2.CodFinanceiroTitulosRenegociacao = titreno.CodFinanceiroTitulosRenegociacao
    
    	Union ALL
    
    	-- Consulta recursiva
    	Select 
    		CTE_TituloOrigem.Cod,
    		(CTE_TituloOrigem.Nivel + 1) as Nivel, 	
    		cast (concat (CTE_TituloOrigem.Caminho, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		CTE_TituloOrigem.CodFinanceiroTitulosOrigem as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 
    		titRenO.CodFinanceiroTitulosRenegociacao,
    		titRenD.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,
    		convert(decimal(20,8),titRenD.Valor) as ValorTituloDestino,
    		convert(decimal(20,8),titRenO.Valor) as ValorRenegociacaoTituloOrigem, 
    		convert(decimal(20,8),titOrigem.Valor)	as ValorTituloOrigem,
    		convert(decimal(20,8),totRen.Valor,0) as ValorTotal,
    		convert(decimal(20,8),totRen2.Valor) as ValorTotalRenegociacaoDestino,
    		convert(decimal(20,8),
    		(CTE_TituloOrigem.ValorRenegociacaoTituloOrigem * 100 / 
    			convert(decimal(20,8), totRen2.Valor)
    		) 
    			* CTE_TituloOrigem.Proporcao / 100
    		) as Proporcao
    
    	From 
    		CTE_TituloOrigem
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.CodFinanceiroTitulos
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenO.CodFinanceiroTitulosRenegociacao = titRenD.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosOrigemRenegociacao titOrigem 
    			on titOrigem.CodFinanceiroTitulos = CTE_TituloOrigem.Cod		
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen 
    			on totRen.CodFinanceiroTitulosRenegociacao = titRend.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen2 
    			on totRen2.CodFinanceiroTitulosRenegociacao = CTE_TituloOrigem.CodFinanceiroTitulosRenegociacao		
    )
    
    , CTE_Titulos as (
    	Select 	
    		cte.Caminho,
    		cte.Nivel, 
    		-- Cod do título de origem
    		cte.CodFinanceiroTitulos,
    		-- Número do título de destino
    		tit.Numero, 
    		tit.Sequencia as Sequencia,
    
    		titO.Cod as CodFinanceiroTitulosOrigem,
    		titO.Numero as NumeroTituloOrigem,
    
    		-- valor do título de destino da renegociação
    		cte.ValorTituloDestino,
    		-- valor total da renegociacao origem (CodFinanceiroTitulosRenegociacao)
    		cte.valortotal as ValorTotalRenegociacaoOrigem,
    		-- valor total da renegociacao destino (CodFinanceiroTitulosRenegociacao)
    		cte.ValorTotalRenegociacaoDestino as ValorTotalRenegociacaoDestino,
    		
    		-- proporção do título na renegociação de origem (primeiro nivel)
    		cte.Proporcao as cteProporcao,
    		cte.ValorRenegociacaoTituloOrigem as valorBaseProporcao,		
    		
    		convert(decimal(20,12), cte.ValorRenegociacaoTituloOrigem) * 100 
    		/ convert(decimal(20,12), cte.ValorTotal)
    		 * cte.Proporcao / 100 
    		 as Proporcao,
    		
    		-- renegociação de origem
    		titO.CodFinanceiroTitulosRenegociacao,
    		-- renegociação em questão
    		cte.CodFinanceiroTitulosRenegociacaoDestino,		
    		-- valor do título de Origem renegociaçao
    		cte.ValorTituloOrigem, 
    		-- valor do título de origem da renegociação até o ultimo nivel de recursividade
    		cte.ValorRenegociacaoTituloOrigem,
    		tit.DataEmissao,
    		titO.DataEmissao as DataTituloOrigem
    	from CTE_TituloOrigem cte 
    	inner Join TAB_FinanceiroTitulos tit on tit.Cod = cte.CodFinanceiroTitulos
    	inner Join TAB_FinanceiroTitulos titO on titO.Cod = cte.CodFinanceiroTitulosOrigem
    )

    Ainda vou refinar um pouco mais, alterar nomes dos campos e outros detalhes para facilitar a manutenção futura.

    Para testes, estou concluindo utilizando a CTE final gerada dessa forma:

    select sum(Pagamento) as Pagamento, tb.Vendedor, tb.Data, tb.numeroorigem, tb.SequenciaOrigem
    from (
    	select 
    		tb.codpagamento, convert(decimal(20,8),tb.ValorPago) as ValorPago, tb.proporcao,tb.CodFinanceiroTitulosOrigem,
    		tb.ValorPagamento, tb.ValorTotal, tb.data,
    		tb.Proporcao
    			* (tb.valorpagamento * 100 / tb.ValorTotalNota)  
    		/ 100 as percentual,
    		Valortotal *  tb.Proporcao
    			* (tb.valorpagamento * 100 / tb.ValorTotalNota)
    			/ 100 
    		/ 100 as Pagamento,
    		tb.numeronota,
    		tb.Numero,
    		tb.NumeroOrigem,
    		tb.SequenciaOrigem,
    		tb.Vendedor
    		from (
    			select
    				convert(decimal(20,8),nt.ValorContabil) as ValorTotalNota, 
    				nt.NumeroNota, convert(decimal(24,12),it.ValorTotal) as ValorTotal,
    				convert(decimal(20,8), pg.Valor) * tp.Proporcao / 100 as ValorPago, 
    				tp.Proporcao as ProporcaoTitulo,  
    				convert(decimal(20,8),pg.Valor) as ValorPagamento, 
    				pg.Cod as codPagamento,
    				pg.Data,
    				tit.Numero as NumeroOrigem,
    				tit.Sequencia as SequenciaOrigem,
    				fun.Descricao as Vendedor,
    				tp.*	
    			from CTE_Titulos tp		
    			inner join TAB_FinanceiroTitulosPagamentos pg on pg.CodFinanceiroTitulos = tp.CodFinanceiroTitulos
    			inner join TAB_FinanceiroTitulos tit on tit.cod = tp.CodFinanceiroTitulosOrigem
    			inner join TAB_Notas nt on tit.CodNotas = nt.Cod
    			inner join TAB_NotasItens it on nt.cod = it.CodNotas
    			inner join TAB_Funcionarios fun on fun.cod = nt.CodVendedor
    			inner join TAB_NaturezaOperacao nat on nat.Cod = nt.CodNaturezaOperacao
    			where pg.CodFinanceiroTitulosRenegociacao = 0
    			and isnull(nat.ComissaoRepresentante,0) <> 0
    			and pg.data >= '01/01/2020'
    			and pg.data <= '13/01/2020'
    	) tb
     ) tb
      group by   tb.Vendedor , tb.codPagamento, tb.data , tb.CodFinanceiroTitulosOrigem, tb.NumeroOrigem, tb.SequenciaOrigem
      order by tb.NumeroOrigem, tb.SequenciaOrigem

    Se alguém quiser posso passar detalhes da modelagem, para entendimento da solução.

    No mais, era isso, muito obrigado!


    Julio C.


    quarta-feira, 25 de março de 2020 02:19
  • Pessoal,

    venho trazer uma notícia que:

    Depois de muito bater cabeça (entre indas e vindas a esta tarefa, em meio a outras tarefas que tive que resolver durante este tempo), com a IMENSA ajuda de vocês, e conversando bastante com a minha equipe na empresa, conseguimos chegar a uma conclusão.

    Analisando, precisava de algumas alterações (links entre as tabelas de Origem e Destino, cálculo da proporção e no final conversões para decimal por causa dos arredondamentos) na CTE recursiva.

    -

    As Renegociações de Títulos tem particularidades nas regras de negócio que tornam extremamente complexa. Podem variar muito.. Por exemplo, pode ter títulos de diversos níveis dentro de uma mesma renegociação (isso é, renegociação DE renegociação.....), também podem haver pagamentos feitos anteriores a uma nova renegociação nos títulos originados por ela, tem a necessidade que um título  DE ORIGEM poder ou não ter NF vinculado (e se não tiver NF o cenário muda, não tem pagamento de comissão por exempl), e vários, vários vários, outros detalhes que tornaram a tarefa ultra-complexa (para o meu nível de conhecimento atual).

    Foi fantástico e imensamente gratificante quando todos os numerozinhos e proporções começaram a fechar, independentemente do nível de cada título da renegociação


    Resolvi da seguinte forma (e desde já me proponho a auxiliar no que eu puder quem tiver demandas semelhantes a esta):


    with TitulosRenegociados as (
    	-- pega títulos de origem em que não estejam como Destino em nenhuma renegociação (nível raiz)
    	Select tit.Cod, tit.CodFinanceiroTitulosRenegociacao From TAB_FinanceiroTitulos tit
    	Inner join TAB_FinanceiroTitulosRenegociacaoTitulos titRenO
    		on titRenO.CodFinanceiroTitulos = tit.Cod and titRenO.Tipo = 'O'
    		and not exists (select titRenD.CodFinanceiroTitulosRenegociacao from TAB_FinanceiroTitulosRenegociacaoTitulos titRenD 
    			where titRenD.CodFinanceiroTitulos = titRenO.CodFinanceiroTitulos
    			and titRenD.Tipo =  'D'
    		)
    ),
    -- Facilita as consultas às tabelas que estão dos títulos renegociados (Origem e Destino), alterando a modelagem
    CTE_TitulosOrigemRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    ),
    CTE_TitulosDestinoRenegociacao as (
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, ren.CodFinanceiroTitulos, ren.Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'D'
    ),
    CTE_TitulosTotalOrigemRenegociacao as (
    	-- valor da renegociação considerando apenas títulos da renegociação de destino	
    	select 
    		ren.CodFinanceiroTitulosRenegociacao, 
    		sum(ren.Valor) as Valor
    	from TAB_FinanceiroTitulosRenegociacaoTitulos ren
    	where ren.Tipo = 'O'
    	group by ren.CodFinanceiroTitulosRenegociacao
    ),
    CTE_Pagamentos as (
    	select pg.CodFinanceiroTitulos, sum(pg.Valor) as Valor from TAB_FinanceiroTitulosPagamentos pg 
    	where isnull(pg.BaixaRenegociacao,0) = 0
    	group by pg.CodFinanceiroTitulos
    ),
    CTE_TituloOrigem as (
    	-- Consulta âncora para a recursividade
    	Select 
    		tr.Cod,
    		1 as Nivel, 
    		cast (concat (tr.Cod, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		titRenO.CodFinanceiroTitulos as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 	
    		tr.CodFinanceiroTitulosRenegociacao ,
    		titRenO.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,		
    		convert(decimal(20,8), titRenD.Valor) as ValorTituloDestino,
    		convert(decimal(20,8), titRenO.Valor) as ValorRenegociacaoTituloOrigem,
    
    		-- na âncora não precisa linkar com o registro de origem (o valor do título de origem será o mesmo)
    		convert(decimal(20,8),titRenO.Valor) as ValorTituloOrigem,
    
    		convert(decimal(20,8),totRen.Valor) as ValorTotal,
    		convert(decimal(20,8),totRen2.Valor) as ValorTotalRenegociacaoDestino,
    		convert(decimal(20,8), 100) as Proporcao
    	From
    		TitulosRenegociados tr 
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = tr.Cod
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenD.CodFinanceiroTitulosRenegociacao = titRenO.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen 
    			 on totRen.CodFinanceiroTitulosRenegociacao = tr.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen2 
    			on totRen2.CodFinanceiroTitulosRenegociacao = titreno.CodFinanceiroTitulosRenegociacao
    
    	Union ALL
    
    	-- Consulta recursiva
    	Select 
    		CTE_TituloOrigem.Cod,
    		(CTE_TituloOrigem.Nivel + 1) as Nivel, 	
    		cast (concat (CTE_TituloOrigem.Caminho, ' > ', titRenD.CodFinanceiroTitulos) as varchar(200)) as Caminho,
    		CTE_TituloOrigem.CodFinanceiroTitulosOrigem as CodFinanceiroTitulosOrigem,
    		titRenD.CodFinanceiroTitulos as CodFinanceiroTitulos, 
    		titRenO.CodFinanceiroTitulosRenegociacao,
    		titRenD.CodFinanceiroTitulosRenegociacao as CodFinanceiroTitulosRenegociacaoDestino,
    		convert(decimal(20,8),titRenD.Valor) as ValorTituloDestino,
    		convert(decimal(20,8),titRenO.Valor) as ValorRenegociacaoTituloOrigem, 
    		convert(decimal(20,8),titOrigem.Valor)	as ValorTituloOrigem,
    		convert(decimal(20,8),totRen.Valor,0) as ValorTotal,
    		convert(decimal(20,8),totRen2.Valor) as ValorTotalRenegociacaoDestino,
    		convert(decimal(20,8),
    		(CTE_TituloOrigem.ValorRenegociacaoTituloOrigem * 100 / 
    			convert(decimal(20,8), totRen2.Valor)
    		) 
    			* CTE_TituloOrigem.Proporcao / 100
    		) as Proporcao
    
    	From 
    		CTE_TituloOrigem
    		Inner Join CTE_TitulosOrigemRenegociacao titRenO
    			 on titRenO.CodFinanceiroTitulos = CTE_TituloOrigem.CodFinanceiroTitulos
    		Inner Join CTE_TitulosDestinoRenegociacao titRenD
    			 on titRenO.CodFinanceiroTitulosRenegociacao = titRenD.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosOrigemRenegociacao titOrigem 
    			on titOrigem.CodFinanceiroTitulos = CTE_TituloOrigem.Cod		
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen 
    			on totRen.CodFinanceiroTitulosRenegociacao = titRend.CodFinanceiroTitulosRenegociacao
    		Inner Join CTE_TitulosTotalOrigemRenegociacao totRen2 
    			on totRen2.CodFinanceiroTitulosRenegociacao = CTE_TituloOrigem.CodFinanceiroTitulosRenegociacao		
    )
    
    , CTE_Titulos as (
    	Select 	
    		cte.Caminho,
    		cte.Nivel, 
    		-- Cod do título de origem
    		cte.CodFinanceiroTitulos,
    		-- Número do título de destino
    		tit.Numero, 
    		tit.Sequencia as Sequencia,
    
    		titO.Cod as CodFinanceiroTitulosOrigem,
    		titO.Numero as NumeroTituloOrigem,
    
    		-- valor do título de destino da renegociação
    		cte.ValorTituloDestino,
    		-- valor total da renegociacao origem (CodFinanceiroTitulosRenegociacao)
    		cte.valortotal as ValorTotalRenegociacaoOrigem,
    		-- valor total da renegociacao destino (CodFinanceiroTitulosRenegociacao)
    		cte.ValorTotalRenegociacaoDestino as ValorTotalRenegociacaoDestino,
    		
    		-- proporção do título na renegociação de origem (primeiro nivel)
    		cte.Proporcao as cteProporcao,
    		cte.ValorRenegociacaoTituloOrigem as valorBaseProporcao,		
    		
    		convert(decimal(20,12), cte.ValorRenegociacaoTituloOrigem) * 100 
    		/ convert(decimal(20,12), cte.ValorTotal)
    		 * cte.Proporcao / 100 
    		 as Proporcao,
    		
    		-- renegociação de origem
    		titO.CodFinanceiroTitulosRenegociacao,
    		-- renegociação em questão
    		cte.CodFinanceiroTitulosRenegociacaoDestino,		
    		-- valor do título de Origem renegociaçao
    		cte.ValorTituloOrigem, 
    		-- valor do título de origem da renegociação até o ultimo nivel de recursividade
    		cte.ValorRenegociacaoTituloOrigem,
    		tit.DataEmissao,
    		titO.DataEmissao as DataTituloOrigem
    	from CTE_TituloOrigem cte 
    	inner Join TAB_FinanceiroTitulos tit on tit.Cod = cte.CodFinanceiroTitulos
    	inner Join TAB_FinanceiroTitulos titO on titO.Cod = cte.CodFinanceiroTitulosOrigem
    )

    Ainda vou refinar um pouco mais, alterar nomes dos campos e outros detalhes para facilitar a manutenção futura.

    Para testes, estou concluindo utilizando a CTE final gerada dessa forma:

    select sum(Pagamento) as Pagamento, tb.Vendedor, tb.Data, tb.numeroorigem, tb.SequenciaOrigem
    from (
    	select 
    		tb.codpagamento, convert(decimal(20,8),tb.ValorPago) as ValorPago, tb.proporcao,tb.CodFinanceiroTitulosOrigem,
    		tb.ValorPagamento, tb.ValorTotal, tb.data,
    		tb.Proporcao
    			* (tb.valorpagamento * 100 / tb.ValorTotalNota)  
    		/ 100 as percentual,
    		Valortotal *  tb.Proporcao
    			* (tb.valorpagamento * 100 / tb.ValorTotalNota)
    			/ 100 
    		/ 100 as Pagamento,
    		tb.numeronota,
    		tb.Numero,
    		tb.NumeroOrigem,
    		tb.SequenciaOrigem,
    		tb.Vendedor
    		from (
    			select
    				convert(decimal(20,8),nt.ValorContabil) as ValorTotalNota, 
    				nt.NumeroNota, convert(decimal(24,12),it.ValorTotal) as ValorTotal,
    				convert(decimal(20,8), pg.Valor) * tp.Proporcao / 100 as ValorPago, 
    				tp.Proporcao as ProporcaoTitulo,  
    				convert(decimal(20,8),pg.Valor) as ValorPagamento, 
    				pg.Cod as codPagamento,
    				pg.Data,
    				tit.Numero as NumeroOrigem,
    				tit.Sequencia as SequenciaOrigem,
    				fun.Descricao as Vendedor,
    				tp.*	
    			from CTE_Titulos tp		
    			inner join TAB_FinanceiroTitulosPagamentos pg on pg.CodFinanceiroTitulos = tp.CodFinanceiroTitulos
    			inner join TAB_FinanceiroTitulos tit on tit.cod = tp.CodFinanceiroTitulosOrigem
    			inner join TAB_Notas nt on tit.CodNotas = nt.Cod
    			inner join TAB_NotasItens it on nt.cod = it.CodNotas
    			inner join TAB_Funcionarios fun on fun.cod = nt.CodVendedor
    			inner join TAB_NaturezaOperacao nat on nat.Cod = nt.CodNaturezaOperacao
    			where pg.CodFinanceiroTitulosRenegociacao = 0
    			and isnull(nat.ComissaoRepresentante,0) <> 0
    			and pg.data >= '01/01/2020'
    			and pg.data <= '13/01/2020'
    	) tb
     ) tb
      group by   tb.Vendedor , tb.codPagamento, tb.data , tb.CodFinanceiroTitulosOrigem, tb.NumeroOrigem, tb.SequenciaOrigem
      order by tb.NumeroOrigem, tb.SequenciaOrigem

    Se alguém quiser posso passar detalhes da modelagem, para entendimento da solução.

    No mais, era isso, muito obrigado!


    Julio C.


    Julio,

    Com certeza seria interessante conhecer sobre a sua modelagem.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 25 de março de 2020 20:32
  • Ola Junior,

    Então , só dar um tempinho e vou postar aqui a estrutura e um preenchimento das tabelas.

    Obrigado pelo retorno.


    Julio C.

    quinta-feira, 26 de março de 2020 11:39