CTE com recursividade

Question

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.

Answer 1

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]

Answer 2

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

Answer 3

Deleted

Answer 4

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

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.

Answer 5

Deleted

Answer 6

Deleted

Answer 7

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.

Answer 8

Deleted

Answer 9

"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.

Answer 10

Deleted

Answer 11

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

Answer 12

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.

Answer 13

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.

Answer 14

Deleted

Answer 15

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!



Answer 16

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.

Answer 17

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

Answer 18

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??

Answer 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.

Answer 20

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]

Answer 21

Ola Junior,

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

Obrigado pelo retorno.

---

Julio C.