*/

quinta-feira, 6 de setembro de 2012

Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo

A idéia primordial desse artigo é demonstrar como desenvolver uma query sql usando um plano de contas ou centro de custo, o principio é o mesmo, subtotalizando de forma invertida, considerando uma estrutura de balancete ou centro de custo, mas sempre usando um plano de contas com contas analiticas e sintéticas e vários niveis de sub-contas.

O SGBDR testados foram:
 Firebird 2.5.1
 Postgres 9.1
 SQL Server 2012
 Oracle 11g R2

Expondo o problema, exemplo:

Montar uma query relatório que mostre o resultado totalizado por níveis de contas lançadas.
O centro de custo irá ser lançado sempre no ultimo nivel e/ou conta analitica, ou seja 1.1.1 ou 1.2.1, etc. 

Conforme figura Figura 01, abaixo:

Figura 01 - Relatório Sumarizado por Plano de Contas

Temos uma estrutura de centro de custo modelada da seguinte maneira: 

Script para Firebird 2.5.1, Postgres 9.1, SQL Server 2012, Oracle 11g R2



-- USE tempdb; -- Descomentar caso use SQL Server 
--DROP TABLE centro_custo;
CREATE TABLE centro_custo
( 
   id_centro_custo  varchar(12) PRIMARY KEY   -- dados da conta ex. 1.02.01 
 , descricao        varchar(50)               -- descricao da conta cadastrada ex. Vendas Externas
 , tipo_conta       varchar(1)                -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check
); 

--DROP TABLE movimento;
CREATE TABLE movimento 
(
   id_movimento   integer   PRIMARY KEY  -- id do movimento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento
 , numero_doc   varchar(40)              -- numero do documento a ser informado  
 , id_centro_custo   varchar(12)         -- chave estrangeira para a tabela centro de custo, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null
 , valor_movimento numeric(15,2)         -- valor informado 
);

Agora iremos povoar a tabela de centro de custo

-- Receitas 

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');

-- Despesas

INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');

-- Vamos povoar a tabela movimento: 

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (1,'0000021','1.1.2',50.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (2,'0000042','1.2.2',100.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (3,'0000084','1.2.2',160.00);

INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (4,'0000142','2.2.1',40.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (5,'0000210','2.2.2',80.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (6,'0000242','2.2.2',20.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (7,'0000284','2.2.1',15.00);

Listando os centros de custos cadastrados ...
-- Listando conteudo de centro_custo 
SELECT * FROM centro_custo ORDER BY 1;
Figura 02 - Lista de Centro Custo

Listando os movimentos cadastrados, referenciando os centros de custos ...
-- Listando conteudo de movimento 
SELECT * FROM movimento;
Figura 03 - Lista de Movimento Script apenas para SQL Server 2012

-- Query SQL Centro de Custo, para SQL Server 2012, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo + '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 
Figura 04 - Query Centro Custo com Resultado em SQL Server 2012

Script apenas para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2
  
-- Query SQL Centro de Custo, para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2, exemplo: 
  SELECT cc.id_centro_custo
       , cc.descricao 
       , sum(m.valor_movimento) AS total_conta 
    FROM centro_custo cc 
    JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo || '%'  -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo 
       , cc.descricao 
ORDER BY cc.id_centro_custo ASC
       ; 
Figura 05 - Query Centro Custo com Resultado em Oracle 11g R2


A grande dica está na junção dos campos de conta usando like concatenado com '%' no final

Como no exemplo com Oracle 11g R2, abaixo descrito:

ON m.id_centro_custo LIKE cc.id_centro_custo || '%'

Existem outras formas de se chegar ao mesmo resultado, uma delas seria implementar union em contas de grupo e depois criar uma view, mas, fica complicado e não elegante, já que, será necessário alterar a query toda vez que o plano de contas mudar a estrutura.

Outra forma seria usar querys recursivas, que seria bem mais elegante que a opção anterior, entretanto, mais complexa, mas quanto ao uso de recursividade em querys, nem todos os SGBDRs atualmente dão suporte a essa técnica, outro problema reside no fato do desempenho e consumo de recursos, pois quanto mais níveis tiver o plano de contas maior será a pilha, em outras palavras, consumo de memória e processamento no servidor do banco de dados alto.

Eu particularmente já tive a oportunidade fazer parte de uma equipe de desenvolvimento, em que o meu chefe e a própria equipe insistiram em traçar uma modelagem que usava querys recursivas, apesar de não concordar, como era novato na equipe, não me deram crédito, então me reservei a sabedoria do silêncio e mesmo não concordando aproveitei a oportunidade para fazer acontecer e vê como ficaria um sistema de custos com uso maciço de querys recursivas, foi divertido, mas ainda não recomendo, pois a complexidade é alta das querys recursivas e da codificação da aplicação também, tornando o compartilhamento do conhecimento difícil, como também a manutenção da aplicação, considerando ainda o consumo extremo de memória e processamento a nível de infra-estrutura.

Mais uma vez espero ter ajudado.


Fique na Paz do Senhor Jesus Cristo !!!

49 comentários:

  1. Fiz um teste utilizando a mesma estrutura no MySql no entanto só me retornou as contas totalizadas sem as (pais) superiores qual a alteração a ser feita para que funcione?

    ResponderExcluir
  2. Só obtive tipo:
    id_centro_custo descricao total_conta

    1.1.2 Escritorio 50
    1.2.2 Brinquedos 260
    2.2.1 Materiais 50

    ResponderExcluir
  3. Olá Anônimo, quando escrevi este artigo, não havia testado em MySQL; realmente no MySQL com "||" vai dá o resultado como você apontou, para solucionar o problema em questão use a função concat do MySQL depois do LIKE, como no exemplo:

    SELECT cc.id_centro_custo
    , cc.descricao
    , sum(m.valor_movimento) AS total_conta
    FROM centro_custo cc
    JOIN movimento m
    ON m.id_centro_custo LIKE concat(cc.id_centro_custo, '%')
    GROUP BY cc.id_centro_custo
    , cc.descricao
    ORDER BY cc.id_centro_custo ASC
    ;

    Eu testei na versão 5.5.20 do MySQL

    SELECT @@version; -- 5.5.20

    Mais uma vez espero ter ajudado!

    ResponderExcluir
  4. Olá! Ajudou sim, ajudou mto a esclarecer minha dúvida. Agradeço a disponibilidade e a presteza com que você esclareceu minha dúvida.

    ResponderExcluir
  5. Outro artigo relacionado
    http://emersonhermann.blogspot.com.br/2013/01/desenvolvendo-querys-sql-para-razao-e.html

    ResponderExcluir
  6. Muito bom o artigo, o problema que estou passando é que em contas de despesas vai de 2.1 até 2.22 então a soma não está indo certa pois 2.1 vai se misturar com as 2.10, 2.11, 2.12 ..., você possui alguma solução para este problema? Obrigado

    ResponderExcluir
    Respostas
    1. Olá Flavio, o moído não é na query e sim na sua estrutura de contas, sugiro que crie uma conta sintética para 2.1 e analíticas para 2.1.01 a 2.1.22, lembrando que é necessário obedecer as mascaras no seu plano de contas exemplo: 9.9.99, neste caso tem-se 3 níveis, sendo 1 digito para primeiro nível, 1 digito para o segundo e dois dígitos para o terceiro nível, lembrando de colocar zeros a esquerda, no seu caso parece que não está sendo considerado isto, porquê, pois no exemplo exposto da conta 2.1 continuou até 9 quando chega em 2.10 até 2.22 sai da mascara com 1 digito para 2 dígitos, o segundo nível teria que ter dois dígitos então todas as contas nesse nível teriam que ter 2 dígitos na mascara, isso, é um problema que os contadores entendem bem.

      Excluir
  7. Por Favor é possível fazer isso no access 2010?? Se for Por Favor apresente a query. Grato Kurt

    ResponderExcluir
    Respostas
    1. Olá Kurt,

      Access não é a minha praia, dada a limitação do mesmo quanto ao SQL. Nos testes que fiz não obtive o resultado desejado. O Máximo que consegui foi totalizar as contas com a query abaixo, a subtotalização de contas talvez seja possível escrevendo em alguma linguagem a exemplo do VB.

      SELECT cc.id_centro_custo
      , cc.descricao
      , sum(m.valor_movimento) AS total_conta
      FROM centro_custo cc
      INNER JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo
      GROUP BY cc.id_centro_custo
      , cc.descricao ;

      Excluir
    2. A subtotalização é possível usando qualquer linguagem de programação invertendo a estrutura de contas, isto é, ordenando em forma decrescente por contas em conjunto com a query informada acima.

      Excluir
  8. Segue um código em VB no link abaixo, bem básico apenas mostrar como funciona o algoritmo.
    http://emersonhermann.blogspot.com.br/2013/12/codigoplanocontasvb.html

    ResponderExcluir
  9. Muito bom este artigo.Veio de encontro ao que estava necessitando. Valeu mesmo.
    Se me permite, gostaria de dar uma colaboração àqueles que trabalham com o mysql: Se quiserem que todo o centro de custo apareça na pesquisa, mesmo que ele esteja sem movimento, é só alterar a sql para esta:
    SELECT cc.id_centro_custo
    , cc.descricao
    , sum(case when m.valor_movimento > 0 then m.valor_movimento else 0 end ) AS total_conta
    FROM centro_custo cc
    LEFT JOIN movimento m
    ON m.id_centro_custo LIKE concat(cc.id_centro_custo, '%')
    GROUP BY cc.id_centro_custo
    , cc.descricao
    ORDER BY cc.id_centro_custo ASC
    ;

    O LEFT JOIN VAI DISPONIBILIZAR TODOS E O CASE JOGARÁ ZERO NOS DADOS SEM REGISTROS.
    Espero que seja útil para alguém.
    Mais uma vez , parabéns pela ajuda . Este site é muito bom. Já compartilhei em minhas redes sociais

    ResponderExcluir
  10. Parabéns pelo Post. Essa técnica de passar o LIKE concatenando com '%' não sabia. Sucesso!!

    ResponderExcluir
  11. SHow De Bola, o problema no meu caso é que demora cerca de 1min:40 segundos dependendo da movimentacao da empresa =( mas a estrutura e os valores funcionaram 100%
    eu estou realizando com o mysql e existe 9 niveis de plano de contas. '-'

    ResponderExcluir
    Respostas
    1. Rafael, geralmente a demora se deve por alguns fatores, o primeiro como o seu BD está configurado, o segundo quantos registros tem em movimento, e o terceiro quantos registros tem em centro_custo. Como solução sugiro algumas opções que são: Opção 1o - Recomendo indexar a coluna id_centro_custo em movimento já que centro_custo elá é PK (indexado por padrão); Opção 2o - Criar view; Opção 3o (MAIS RECOMENDÁVEL) - Criar store procedures e/ou stores functions; Opção 4o (MAIS COMPLEXA) - Criar uma tabela de saldo como movimento encerrado e acessar somente movimento selecionado: Opção 5o (NUNCA FIZ EM TODOS) - Testar em outros SGBDR, a exemplo do Oracle 11g R2, que é o meu caso, funciona com excelente desempenho, também testei com Firebird, mas o desempenho não foi bom; Opção 6o - Usar um mix de todas as opções citadas anteriormente.

      Excluir
    2. Opção 7o - Rever configurações do Servidor de Aplicação e BD
      PS: Também quanto mais níveis, maior será a demanda (tempo) para processar.

      Excluir
  12. Ola, Grato por suas opções, oq eu fiz foi rever as configuracoes do BD consegui diminuir 40 segundos rsrs.
    Ja fiz os index e ta bem mais rapido do que antes.
    Mas vou optar pela opcao 3 mesmo. ^^
    Grato por sua resposta e grato pelo post :D

    ResponderExcluir
    Respostas
    1. Apenas como demostração de caso de sucesso, eu implementei a mestra estrutura e conhecimentos, demonstrados nesse artigo e em outro [1] complementar, escritos neste blog em um Sistema de Contabilidade padrão COSIF usando linguagem de programação PHP e PLSql sendo banco de dados Oracle 11g R2, e posso te dizer que: Além da exatidão, item obrigatório, o desempenho é excelente, prentendo também testar com Postgres usando PG/PlSql, pelo que conheço o Postgres creio que os testes serão bem-sucedidos.
      [1] http://emersonhermann.blogspot.com.br/2013/01/desenvolvendo-querys-sql-para-razao-e.html

      Excluir
  13. Testei no firebird e funcionou 100%, muito obrigado!
    Pablo George

    ResponderExcluir
  14. Boa tarde esta testando em uma estrutura muito parecei em Oracle mas esta retornando somente as contas analíticas.
    Sera que estou fazendo algo errado?
    tentei com || TBM.
    select p.conta
    , p.descricao
    , SUM(s.valorcredito) - SUM(s.valordebito)
    from ct_planoconta p
    join ct_saldo s on (s.conta like concat(p.conta, '%'))
    where s.ano = '2017' and s.mes = '08' and p.nroempresa = 1 and s.filial = 1 and s.conta between '31000000' and '43101002'
    group by p.conta, p.descricao
    order by p.conta ASC

    ResponderExcluir
    Respostas
    1. Olá Felipe!!!

      Já tentou substituir essa parte do código:
      on (s.conta like concat(p.conta, '%'))
      Por este?:
      on s.conta like p.conta || '%'

      Qual a versão do Oracle ?

      Faz o teste!

      Excluir
    2. Este comentário foi removido pelo autor.

      Excluir
    3. Versão é 11
      Ja fiz desta forma que esta passando, mas só traz as contas analíticas.
      Na tabela de saldo so consta as contas analíticas, somente na tabela do plano de conta constam as sintéticas

      Excluir
    4. Olá Felipe!!! Eu uso essa mesma técnica adaptado para os sistemas que desenvolvo com BD Oracle 11 R2 e funciona sem stress. Me manda um email emersonhermann@gmail.com e tentaremos vê qual é o problema.

      Excluir
  15. Bom dia, esta técnica me ajudou bastante mas resolveu em parte o meu problema. O meu cliente deseja saber os resultados das receitas e despesas DENTRO DE UM PERÍODO.
    Seria possível fazer isto aproveitando a mesma rotina?

    Sem mais, agradeço.
    Hamilton Silva

    ResponderExcluir
    Respostas
    1. Eita, muito simples, basta passar como parâmetro no JOIN depois do '%'.

      Valeu.

      Excluir
    2. Só acrescentando o que Hamilton Rodrigues comentou, implementaria um campo do tipo data na tabela movimento e na cláusula WHERE da consulta usar BETWEEN

      Excluir
    3. Seria um relatório de razão com as contas analíticas filtrada por datas

      Excluir
  16. Boa tarde,

    Parabéns pelo artigo, porém no meu caso eu uso na FK da tabela movimento com o plano de contas o campo ID automatico e não a classificação.

    Teria algum forma de adaptar esse SQL para esse caso em específico?

    Obrigado

    ResponderExcluir
    Respostas
    1. Considerando como campos m.id_centro_custo (fk), cc.id_centro_custo (pk) ambos tipo integer e um campo conta do tipo varchar na tabela centro_custo com as mascaras das contas, não testei, mas, a principio faria assim:

      Essa parte do código:
      FROM centro_custo cc
      JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo || '%' -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE

      Alteraria para:
      FROM centro_custo cc
      JOIN movimento m
      ON m.id_centro_custo = cc.id_centro_custo
      AND LIKE cc.conta || '%'

      Faz o teste!

      Excluir
    2. Um exemplo de solução com a modelagem supracitada:
      http://sqlfiddle.com/#!9/065b92/8

      Excluir
  17. Rapaz, você é 10! Me ajudou muito este post!

    ResponderExcluir
  18. Este comentário foi removido pelo autor.

    ResponderExcluir
  19. Muito bom o artigo, parabéns!! Só tenho uma curiosidade: Como você faria para criar uma tabela de Centros de Custo com a identação demonstrada no artigo? Tipo, se fosse criar um Centro de Custo do zero, como seria essa tabela?

    ResponderExcluir
    Respostas
    1. Penso que esta pergunta está mais ligada ao negócio em si do que a Tecnologia da Informação, pois centro de custo muda de empresa para empresa em seus ramos de economia, por exemplo: Centro de custo de indústrias são bem diferentes de comércio e ou serviço, e cada empresa tem sua particularidade. Penso que não mudaria a estrutura proposta neste artigo, apenas adaptaria as contas para cada necessidade, considerando máscara e níveis de contas.

      Excluir
    2. Na configuração do Plano de Contas, ter uma ajuda profissional especializada de um Contador ou Administrador seria bastante interessante.

      Excluir
  20. Utilizo o mysql, não consegui converter a query para o meu caso, não utilizo a classificação no movimento, uso o código do plano conta, direto no registro do movimento, conseguiria me ajudar?
    Muito obrigado

    ResponderExcluir
    Respostas
    1. Posso sim! Me manda um email (emersonhermann@gmail.com) e tentaremos vê qual é o problema

      Excluir
    2. Já o fiz, muito obrigado pelo retorno.

      Excluir
  21. Meus parabéns pelo post!
    Só uma dúvida (para quem puder ajudar):
    Como você faria para inserir, de forma automática, uma nova hierarquia (sem que seja campo de texto livre, para evitar erros).
    Exemplo:
    Se eu tenho:
    01 - Receitas
    01.01 - Vendas
    02 - Despesas
    02.01 - Fornecedores
    02.02 - Funcionários
    02.02.01 - Salários

    Gostaria de inserir, seguindo a tabela acima, o item:
    02.02.02 - Comissões mas de maneira que o banco calculasse a hierarquia se eu apenas dissesse, por exemplo, o grupo pai (que neste caso, seria 02.02 - Funcionários) mas, sabendo que já existe o subgrupo 02.02.01 o banco já fizesse a inserção correta de hierarquia (02.02.02) sem a necessidade de se fazer vários SELECTS.

    Qualquer informação é muito bem-vinda.
    Obrigado.

    ResponderExcluir
    Respostas
    1. Olá Marcelo!
      Já apanhei muito para montar um plano de contas, algo que não é a minha praia, pois exige muito planejamento, dai vem a necessidade do papel do Contador ou Administror
      Mas quando a nacessidade é mais técnica ai sim, está no meu escopo.
      No seu caso faria assim no SELECT:

      SELECT cc.id_centro_custo
      , cc.descricao
      , sum(m.valor_movimento) AS total_conta
      FROM centro_custo cc
      JOIN movimento m
      ON m.id_centro_custo LIKE cc.id_centro_custo || '%' -- trocar por +'%' se for usar SQL SERVER
      WHERE cc.id_centro_custo LIKE '02.02%'
      GROUP BY cc.id_centro_custo
      , cc.descricao
      ORDER BY cc.id_centro_custo ASC
      ;
      Espero ter ajudado!

      Excluir
  22. Que a paz de Nosso Senhor Jesus Cristo esteja reinando em vosso lar.
    Estou desenvolvendo um banco de dados que justamente vai de encontro a esse artigo que está sendo muito esclarecedor.
    No exemplo acima percebi que no exemplo da direita na linha 34 tem ")m" seria um apelido para o select acima?
    Outra coisa na linha seguinte "ON m.classificacao LIKE" onde que o "m" se refere a tabela "movimento_caixa" ao observar ela não vi em um primeiro momento a coluna "classificacao".
    Se for possível esclarecer fico muito grato.
    Que Deus continue abençoando sua vida.

    ResponderExcluir
    Respostas
    1. Qual o banco de dados (SGBDR) você está tentando rodar o script desse artigo? Passa um email para mim. (emersonhermann@gmail.com)

      Excluir
  23. JOIN movimento m
    ON m.id_centro_custo LIKE cc.id_centro_custo + '%' -- O segredo está aqui

    ResponderExcluir