Você já teve que diluir o valor de uma despesa ao longo de vários centros de custos? Ou então de diluir o valor de frete entre os vários volumes transportados?
1. Introdução
Recentemente, em grupo de sql server no whatsapp foi solicitada sugestão sobre como fazer o rateio do valor total da nota usando como peso a quantidade de unidades vendidas por item. E então me lembrei que ocasionalmente aparecem solicitações semelhantes, variando o que deve ser rateado e quais os critérios de rateio.
Para desenvolvimento deste artigo foi criado conjunto de tabelas registrando vendas e respectivos produtos vendidos; o código SQL de criação destas tabelas está ao final deste artigo, no anexo.
Em cada venda consta o valor cobrado de frete e o objetivo é ratear o valor cobrado de frete da venda realizada pelo valor total de cada item. É necessário que a soma dos rateios seja igual ao do valor do frete.
Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.
2. Uma solução
2.1. Razão e proporção
Para calcular o rateio vamos utilizar os conceitos matemáticos descritos no tópico Razão e proporção:
Para efetuar o rateio de valores é necessário primeiro calcular a razão entre os valores secundários e o valor principal. Por exemplo, supondo que em uma venda o valor total foi de $500,00 e que o valor de frete foi de $80,00, constando a venda de 3 itens nos valores de $150,00, $100,00 e $250,00. Neste caso, qual é a razão entre os valores secundários (o valor de cada item) e o valor principal (o valor total da venda)?
O segundo conceito que será utilizado é o de proporção:
Ao aplicar o conceito de proporção obtemos o rateio para cada um dos itens da venda. Basta multiplicarmos a razão de cada item pelo valor do frete, obtendo-se assim o rateio:
A solução para o problema de rateio envolve então o uso desses dois conceitos matemáticos.
2.2. Desenvolvimento da solução
O esboço de código SQL para calcular o rateio do frete é
-- código #2.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
SELECT IV.Numero_NF, IV.Numero_item, IV.Valor_total_item,
V.Valor_total_venda, V.Valor_frete,
(IV.Valor_total_item / V.Valor_total_venda) as Razao,
cast ((V.Valor_frete * (IV.Valor_total_item / V.Valor_total_venda)) as decimal(8,2)) as Rateio
from VENDA as V
inner join ITEM_VENDA as IV on IV.Numero_NF = V.Numero_NF;
O resultado do código SQL acima é o seguinte:
Para cada venda o valor do frete foi diluído proporcionalmente considerando-se o valor da coluna Razao. Para uma mesma venda, a soma dos valores da coluna Rateio deve ser o mesmo que o da coluna Valor_frete da venda. Entretanto, nem sempre isto ocorre, por causa de arredondamentos. Para avaliar se houve rateios incorretos, é necessário então somar os valores da coluna Rateio de cada venda, conforme o seguinte código SQL:
-- código #2.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
with
cRazao as (
SELECT IV.Numero_NF, IV.Numero_item, IV.Valor_total_item,
V.Valor_total_venda, V.Valor_frete,
Razao= (IV.Valor_total_item / V.Valor_total_venda)
from VENDA as V
inner join ITEM_VENDA as IV on IV.Numero_NF = V.Numero_NF
),
cProporcao as (
SELECT Numero_NF, Numero_item, Valor_total_item,
Valor_total_venda, Valor_frete, Razao,
Proporcao= cast ((Valor_frete * Razao) as decimal(8,2))
from cRazao
)
SELECT Numero_NF, Numero_item, Valor_frete, Proporcao,
Soma_proporcao= sum (Proporcao) over (partition by Numero_NF)
into #Rateio_Frete
from cProporcao;
A primeira cte, cRazao, calcula a razão para cada item vendido e a segunda cte, cProporcao, calcula a proporção do valor de frete para cada item vendido. Ou seja, a aplicação dos conceitos descritos no item 2.1.
O resultado da execução do código #2.2 é o seguinte:
Observe que para a venda cujo valor de Numero_NF é 043659 o valor do frete foi de $616,10 mas a soma dos rateios é menor: $616,07. Por causa de arredondamentos há uma diferença de $0,03.
Para obter relação de vendas que necessitam de ajustes no rateio pode-se executar o seguinte código SQL:
-- código #2.3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
SELECT distinct Numero_NF
from #Rateio_Frete
where Valor_frete <> Soma_proporcao;
Quando ocorrem diferenças é necessário realizar ajustes nos valores de rateios, até que soma dos rateios coincida com o valor total da venda.
Um critério é aplicar a diferença diretamente no maior valor de rateio, para cada venda. Por exemplo, no resultado do código #2.2 para a venda cujo número de nota fiscal é 043659 percebe-se que o maior valor é $181,99, referente ao número de item 11. Ao utilizar este critério basta adicionar $0,03, obtendo-se $182,02. Desta forma a soma das proporções fica igual ao valor do frete.
A implementação deste critério é a seguinte:
-- código #2.4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
with
cDiferenca as (
SELECT *,
Diferenca= (Valor_frete - Soma_proporcao),
Seq= row_number() over (partition by Numero_NF
order by Proporcao desc)
from #Rateio_Frete
where Valor_frete <> Soma_proporcao
)
UPDATE RF
set Proporcao= RF.Proporcao + RF.Diferenca
from cDiferenca as RF
where RF.Seq = 1;
Outro critério é ajustar nos maiores valores de rateio de cada venda em que ocorreu diferença. O primeiro passo é calcular qual é a diferença entre o valor total da venda e a soma dos rateios; a seguir diluir essa diferença iniciando pelos maiores valores de rateio. No caso deste exemplo o menor valor é $0,01; então este deve ser o valor a ser ajustado em algumas das linhas dos itens vendidos. E, é óbvio, somente deve-se alterar a quantidade de linhas suficientes para eliminar a diferença.
-- código #2.5
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
with
cDiferenca as (
SELECT *,
Diferenca= (Valor_frete - Soma_proporcao),
Seq= row_number() over (partition by Numero_NF
order by Proporcao desc)
from #Rateio_Frete
where Valor_frete <> Soma_proporcao
),
cAjuste as (
SELECT *,
Ajuste= cast (sign (Diferenca) * 0.01 as decimal(5,2)),
Qtd_linhas= cast (abs ((Diferenca)) / 0.01 as integer)
from cDiferenca
)
UPDATE RF
set Proporcao= RF.Proporcao + RF.Ajuste
from cAjuste as RF
where RF.Seq <= RF.Qtd_linhas;
No código SQL acima a cte cAjuste faz os cálculos necessários para a atualização. A coluna Qtd_Linhas calcula a quantidade de linhas a alterar, por venda. Por exemplo, se a diferença é de $0,02 e o ajuste será centavo a centavo, então somente duas linhas da venda devem ser alteradas.
Após o ajuste nos rateios é necessário recalcular os valores da coluna soma_Proporcao, para verificar se não ficou algum resíduo.
-- código #2.6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
-- ajusta valor da coluna Soma_proporcao
with refazSoma as (
SELECT Numero_NF, Numero_item, Soma_proporcao,
sum (Proporcao) over (partition by Numero_NF) as novaSoma
from #Rateio_Frete
where Valor_frete <> Soma_proporcao
)
UPDATE refazSoma
set Soma_proporcao= novaSoma;
Após rodar o código SQL acima você pode rodar novamente o código #2.3. Se ainda for listada alguma linha, então deve-se analisar o que ocorreu.
3. Finalizando
3.1. Observações
Neste artigo foi apresentada uma solução para o rateio proporcional de valores, usando como exemplo o rateio de valor de frete. Optou-se pelo uso de tabela temporária mas este problema também pode ser resolvido utilizando-se CTE encadeadas.
3.2. Material de estudo
4. Anexo
4.1 Criação e carga das tabelas
Estrutura das tabelas:
-- código #4.1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
CREATE TABLE VENDA (
Numero_NF char(6) not null,
Valor_total_venda decimal(8,2) not null,
Valor_frete decimal(6,2) not null,
Valor_total_nota as cast ((Valor_total_venda + Valor_frete) as decimal(8,2)),
constraint I1_VENDA primary key (Numero_NF)
);
CREATE TABLE ITEM_VENDA (
Numero_NF char(6) not null
references VENDA (Numero_NF),
Numero_item tinyint not null,
Cod_produto smallint not null,
Qtd_unidades decimal (5,2) not null,
Valor_unitario decimal(8,2) not null,
Valor_total_item as cast ((Qtd_unidades * Valor_unitario) as decimal(8,2)),
constraint I1_ITEM_VENDA primary key (Numero_NF, Numero_item)
);
Os valores para carga das tabelas são importados do banco de dados Adventure Works.
-- código #4.2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com
----- VENDA
-- insere as linhas de vendas
INSERT into VENDA (Numero_NF, Valor_total_venda, Valor_frete)
SELECT right (('00000' + cast (SalesOrderID as varchar(6))), 6),
0, cast (round (Freight, 2) as decimal(6,2))
from AdventureWorks.Sales.SalesOrderHeader
where SalesOrderID < 1000000;
----- ITEM_VENDA
-- remove o índice primário
ALTER TABLE ITEM_VENDA DROP CONSTRAINT I1_ITEM_VENDA;
-- insere as linhas de itens de venda
INSERT into ITEM_VENDA (Numero_NF, Numero_item, Cod_produto, Qtd_unidades, Valor_unitario)
SELECT right (('00000' + cast (SalesOrderID as varchar(6))), 6),
cast (0 as tinyint), cast (ProductID as smallint),
cast (OrderQty as decimal(5,2)),
cast (UnitPrice as decimal(8,2))
from AdventureWorks.Sales.SalesOrderDetail;
-- gera numeração de item por venda
with Sequenciado as (
SELECT Numero_NF, Numero_item,
row_number() over (partition by Numero_NF
order by Cod_produto) as Seq
from ITEM_VENDA
)
UPDATE Sequenciado
set Numero_item= Seqset Numero_item= Seq;
-- regera o índice primário
ALTER TABLE ITEM_VENDA
ADD CONSTRAINT I1_ITEM_VENDA primary key (Numero_NF, Numero_item);
----- VENDA
-- atualiza valor total de cada venda
with cValor as (
SELECT Numero_NF, sum (Valor_total_item) as somaVendas
from ITEM_VENDA
group by Numero_NF
)
UPDATE V
set Valor_total_venda= CV.somaVendas
from VENDA as V
inner join cValor as CV on CV.Numero_NF = V.Numero_NF;
Porto SQL 