Rateio proporcional de valores

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

Razão e proporção

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;