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

28 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