Inquiridor
CTE com recursividade

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
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]
-
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
-
-
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.
- Editado Julio Costi segunda-feira, 25 de novembro de 2019 19:12
-
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
-
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
-
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.
-
-
"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
-
-
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
-
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.
-
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.
-
-
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,00Está 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
-
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.
-
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
-
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-14Tí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 %
-
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.
- Editado Julio Costi quarta-feira, 25 de março de 2020 11:28
- Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 25 de março de 2020 20:31
-
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]
-