terça-feira, 29 de janeiro de 2013

Desenvolvendo querys SQL para Razão e Balancete Contábil.

Desenvolvendo querys SQL para Razão e Balancete Contábil.

Em um artigo escrito neste blog no dia 06-09-2012 Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo conforme link http://www.emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html, mostrei como desenvolver uma query em uma estrutura de plano de contas ou centro de custo, dessa vez irei apresentar de forma prática, gradual e por exemplos de como desenvolver uma query para relatório de Razão, Razão Sumarizado e Balancete Contábil.

Nivel de Complexidade: Intermediário, Avançado

Quem desenvolve sabe como é complicado criar relatórios para contabilidade ou centro de custos, seja codificando em alguma linguagem ou mesmo tentando encurtar o tempo ou a pressão, usando algum gerador de relatórios, a exemplo do MS Report Service, SAP Crystal Reports, Script Case, etc.

As querys scripts mostradas nesse artigo foram desenvolvidas para os seguintes SGBDRs em ordem alfabética:

Firebird 2.5.1
Oracle 11g R2
Postgres 9.1
SQL Server 2012

A modelagem aqui apresentada não seguiu o rigor acadêmico, a exemplo de implementação de constraints, chaves primárias, etc, bem como os exemplos citados para area Contabil.

O objetivo principal deste artigo é mostrar como desenvolver querys para Sistemas Contabeis, houve uma simplificação com intuíto de facilitar o entendimento, entretanto, com os exemplos expostos é possivel adotar em qualquer modelo relacional.

Houve também uma preocupação em apresentar os scripts desenvolvidos nos SGBDRs citados no inicio deste documento.

Pode-se chegar aos mesmos resultados de uma forma mais performática e simples; estou apto a sugestões.

Seguem os exemplos em scripts SQL:

Criando as tabelas ... (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)

-- USE tempdb; -- Descomentar caso use SQL Server, recomendo criar um banco de teste para os outros SGBDRs.
--DROP TABLE plano_conta;
CREATE TABLE plano_conta
( 
   id_plano_conta   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 lancamento;
CREATE TABLE lancamento 
(
   id_lancamento    integer   PRIMARY KEY    -- id do lancamento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento
 , dt_lancamento    date                     -- data do lancamento 
 , numero_doc       varchar(40)              -- numero do documento a ser informado  
 , id_plano_conta   varchar(12)              -- chave estrangeira para a tabela plano_conta, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null
 , tipo_lancamento  varchar(1)               -- tipo de lancamento 'E' = Entrada 'S' = Saida 
 , historico        varchar(100)             -- historico do lancamento 
 , valor_lancamento numeric(15,2)            -- valor informado 
);

Povoando os tabelas...
-- Receitas 
 
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
 
-- Despesas
 
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
 
-- Vamos povoar a tabela lancamento: 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (1,'2012-07-03','0000084','1.2.2','E',NULL,10.55); 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (2,'2012-07-03','0000084','1.2.2','S',NULL,2.50); 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (3,'2012-07-01','0000021','1.1.2','E',NULL,50.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento,numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (4,'2012-07-01','0000042','1.2.2','E',NULL,100.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (5,'2012-07-04','0000084','1.2.2','E',NULL,160.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (6,'2012-07-04','0000084','1.2.2','S',NULL,80.00);
 
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (7,'2012-07-04','0000142','2.2.1','S',NULL,40.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (8,'2012-07-07','0000210','2.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (9,'2012-07-13','0000242','2.2.2','S',NULL,20.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (10,'2012-07-13','0000284','2.2.1','S',NULL,15.00);



Vamos as querys relatórios...

Razão Detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012) 

   SELECT lan.id_lancamento
        , lan.dt_lancamento 
        , lan.numero_doc
        , lan.id_plano_conta
        , lan.tipo_lancamento
        , lan.historico 
        , 
          coalesce(
          (
          SELECT sum
                 ( 
                  CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento
                       WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento * -1 
                       ELSE 
                            0.00
                  END
                 ) 
            FROM lancamento 
            WHERE dt_lancamento < lan.dt_lancamento 
              AND id_plano_conta  = lan.id_plano_conta 
          ),0)  AS saldo_inicial  
        , CASE WHEN tipo_lancamento = 'E' THEN 
                    valor_lancamento
               ELSE 
                    0.00 
          END AS entrada 
        , CASE WHEN tipo_lancamento = 'S' THEN 
                    valor_lancamento
               ELSE 
                    0.00 
          END AS saida 
        ,  
          coalesce(
          (
          SELECT sum
                 ( 
                  CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento 
                       WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento * -1 
                       ELSE 
                            0.00
                  END
                 ) 
            FROM lancamento
           WHERE dt_lancamento <= lan.dt_lancamento 
             AND id_plano_conta  = lan.id_plano_conta 
          ),0)  AS saldo_final
     FROM lancamento AS lan
     JOIN plano_conta AS plc 
       ON plc.id_plano_conta  = lan.id_plano_conta
    WHERE lan.dt_lancamento >= '2012-07-04'
      AND lan.dt_lancamento <= '2012-07-14'
      AND lan.id_plano_conta  = '2.2.2'
 ORDER BY lan.dt_lancamento ASC 
        ;


Razão Sumarizado Por Plano de Conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao sumarizado por plano de conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)   
    SELECT x.id_plano_conta
         , pcx.descricao 
         , coalesce(
           (
            SELECT sum
                   ( 
                    CASE WHEN tipo_lancamento = 'E' THEN 
                              valor_lancamento
                         WHEN tipo_lancamento = 'S' THEN 
                              valor_lancamento * -1 
                         ELSE 
                              0.00
                     END
                   ) 
              FROM lancamento 
             WHERE dt_lancamento < '2012-07-04'
               AND id_plano_conta  = x.id_plano_conta 
           ),0)  AS saldo_inicial 
         , sum(x.entrada) AS entrada
         , sum(x.saida)   AS saida
         , coalesce(
           (
            SELECT sum
                   ( 
                    CASE WHEN tipo_lancamento = 'E' THEN 
                              valor_lancamento 
                         WHEN tipo_lancamento = 'S' THEN 
                              valor_lancamento * -1 
                         ELSE 
                              0.00
                    END
                   ) 
              FROM lancamento
             WHERE dt_lancamento <=  '2012-07-14'
               AND id_plano_conta  = x.id_plano_conta 
           ),0)  AS saldo_final
      FROM
         ( 
           SELECT lan.id_plano_conta
                , CASE WHEN tipo_lancamento = 'E' THEN 
                            valor_lancamento
                       ELSE 
                            0.00 
                  END AS entrada 
                , CASE WHEN tipo_lancamento = 'S' THEN 
                            valor_lancamento
                       ELSE 
                            0.00 
                  END AS saida 
             FROM lancamento AS lan
             JOIN plano_conta AS plc 
               ON plc.id_plano_conta  = lan.id_plano_conta
            WHERE lan.dt_lancamento >= '2012-07-04'
              AND lan.dt_lancamento <= '2012-07-14'
              AND lan.id_plano_conta  = '2.2.2'
         ) AS x
      JOIN plano_conta AS pcx
        ON pcx.id_plano_conta = x.id_plano_conta 
  GROUP BY x.id_plano_conta
         , pcx.descricao
  ORDER BY x.id_plano_conta ASC 
      ;


Balancete Contabil (Script para SQL Server 2012)
-- balancete contabil (Script para SQL Server 2012) 
 SELECT pcx.id_plano_conta
       , pcx.descricao 
       , sum(xx.saldo_inicial) AS saldo_inicial 
       , sum(xx.entrada)       AS entrada
       , sum(xx.saida)         AS saida 
       , sum(xx.saldo_final)   AS saldo_final 
    FROM
       (
        SELECT x.id_plano_conta
             , coalesce(
               (
                SELECT sum
                       ( 
                        CASE WHEN tipo_lancamento = 'E' THEN 
                                  valor_lancamento
                             WHEN tipo_lancamento = 'S' THEN 
                                  valor_lancamento * -1 
                             ELSE 
                                  0.00
                        END
                       ) 
                  FROM lancamento 
                 WHERE dt_lancamento < '2012-07-04'
                   AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_inicial 
             , sum(x.entrada) AS entrada
             , sum(x.saida)   AS saida
             , coalesce(
               (
                 SELECT sum
                        ( 
                         CASE WHEN tipo_lancamento = 'E' THEN 
                                   valor_lancamento 
                              WHEN tipo_lancamento = 'S' THEN 
                                   valor_lancamento * -1 
                              ELSE 
                                   0.00
                         END
                        ) 
                   FROM lancamento
                  WHERE dt_lancamento <=  '2012-07-14'
                    AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_final
          FROM
             ( 
               SELECT lan.id_plano_conta
                    , CASE WHEN lan.tipo_lancamento = 'E' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS entrada 
                    , CASE WHEN lan.tipo_lancamento = 'S' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS saida 
                 FROM lancamento AS lan
                WHERE lan.dt_lancamento >= '2012-07-04'
                  AND lan.dt_lancamento <= '2012-07-14'
                --AND lan.id_plano_conta  = '2.2.2'
             ) AS x
      GROUP BY x.id_plano_conta
       ) AS xx
    JOIN plano_conta pcx 
      ON xx.id_plano_conta LIKE pcx.id_plano_conta + '%'
GROUP BY pcx.id_plano_conta 
       , pcx.descricao 
ORDER BY pcx.id_plano_conta ASC
       ; 


Balancete Contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
-- balancete contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)   
  SELECT pcx.id_plano_conta
       , pcx.descricao 
       , sum(xx.saldo_inicial) AS saldo_inicial 
       , sum(xx.entrada)       AS entrada
       , sum(xx.saida)         AS saida 
       , sum(xx.saldo_final)   AS saldo_final 
    FROM
       (
        SELECT x.id_plano_conta
             , coalesce(
               (
                SELECT sum
                       ( 
                        CASE WHEN tipo_lancamento = 'E' THEN 
                                  valor_lancamento
                             WHEN tipo_lancamento = 'S' THEN 
                                  valor_lancamento * -1 
                             ELSE 
                                  0.00
                        END
                       ) 
                  FROM lancamento 
                 WHERE dt_lancamento < '2012-07-04'
                   AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_inicial 
             , sum(x.entrada) AS entrada
             , sum(x.saida)   AS saida
             , coalesce(
               (
                 SELECT sum
                        ( 
                         CASE WHEN tipo_lancamento = 'E' THEN 
                                   valor_lancamento 
                              WHEN tipo_lancamento = 'S' THEN 
                                   valor_lancamento * -1 
                              ELSE 
                                   0.00
                         END
                        ) 
                   FROM lancamento
                  WHERE dt_lancamento <=  '2012-07-14'
                    AND id_plano_conta  = x.id_plano_conta 
               ),0)  AS saldo_final
          FROM
             ( 
               SELECT lan.id_plano_conta
                    , CASE WHEN lan.tipo_lancamento = 'E' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS entrada 
                    , CASE WHEN lan.tipo_lancamento = 'S' THEN 
                                lan.valor_lancamento
                           ELSE 
                                0.00 
                      END AS saida 
                 FROM lancamento AS lan
                WHERE lan.dt_lancamento >= '2012-07-04'
                  AND lan.dt_lancamento <= '2012-07-14'
                --AND lan.id_plano_conta  = '2.2.2'
             ) AS x
      GROUP BY x.id_plano_conta
       ) AS xx
    JOIN plano_conta pcx 
      ON xx.id_plano_conta LIKE pcx.id_plano_conta || '%'
GROUP BY pcx.id_plano_conta 
       , pcx.descricao 
ORDER BY pcx.id_plano_conta ASC
       ; 


Figura 01 - Resultado das Querys

Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.

Mais uma vez espero ter ajudado.

APDSJ!

15 comentários:

  1. Tem como fazer o mesmo em MySQL? ou existe restrição a esses BD's que vc citou?

    ResponderExcluir
    Respostas
    1. Tem sim, porém teria que ser alterado algumas tópicos por exemplo:
      sum
      (
      CASE WHEN tipo_lancamento = 'E' THEN
      valor_lancamento
      WHEN tipo_lancamento = 'S' THEN
      valor_lancamento * -1
      ELSE
      0.00
      END
      )
      ; isso não funcionaria no MySQL teria que fazer em várias colunas, para dá o mesmo resultado.
      O outro tópico que também não funcionaria no MySQL seria
      ON xx.id_plano_conta LIKE pcx.id_plano_conta || '%'
      teria que ser adaptado para:
      ON xx.id_plano_conta LIKE concat(pcx.id_plano_conta,'%')
      ;
      É possível sim, porém será necessário fazer muitas mudanças na query para funcionar.

      Cordialmente,

      Excluir
    2. Teria como vc mostrar tentei diversas vezes mas não consegui. Sou iniciante em bancos de dados, estudante e me interessei mto pelo seu artigo.

      Excluir
    3. -- razao detalhado - Mysql 5
      SELECT lan.id_lancamento
      , lan.dt_lancamento
      , lan.numero_doc
      , lan.id_plano_conta
      , lan.tipo_lancamento
      , lan.historico
      , coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento < lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'E'
      ),0.00) -
      coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento < lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'S'
      ),0.00) AS saldo_inicial
      , coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento = lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'E'
      ),0.00) AS entrada
      , coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento = lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'S'
      ),0.00) AS saida
      , coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento <= lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'E'
      ),0.00)-
      coalesce(
      (
      SELECT sum(valor_lancamento)
      FROM lancamento
      WHERE dt_lancamento <= lan.dt_lancamento
      AND id_plano_conta = lan.id_plano_conta
      AND tipo_lancamento = 'S'
      ),0.00) AS saldo_final
      FROM lancamento AS lan
      JOIN plano_conta AS plc
      ON plc.id_plano_conta = lan.id_plano_conta
      WHERE lan.dt_lancamento >= '2012-07-04'
      AND lan.dt_lancamento <= '2012-07-14'
      AND lan.id_plano_conta = '2.2.2'
      ORDER BY lan.dt_lancamento ASC
      ;

      -- os demais relatórios vc obtém com base nessa query.

      Excluir
  2. Agradeço muito a dica, gostei muito do seu blog. Vai me ajudar muito a entender a diferença entre as sintaxes de cada banco de dados, muito obrigado mesmo!

    ResponderExcluir
  3. Olá Peregrino, tudo bem. Preciso de uma dica do amigo. Tenho uma tabela de lançamentos no PostgreSQL 9.1 para com a estrutura:
    idlancto
    datalan
    debito
    credito
    valor
    historico
    complemento
    complementolongo
    Tem como ajustar o balancete para esta estrutura ?
    Obrigado.
    Paulo.

    ResponderExcluir
  4. Olá Paulo,

    A priori, tem como fazer um extrato com débito e crédito e saldo anterior e saldo atual, se idlancto for estruturado em contas tipo 1.01.01.01 tem como fazer balancete também.

    ResponderExcluir
  5. Boa tarde Peregrino, gostei do seu trabalho. Vou seguir e tentar adaptar para uma aplicação e em breve envio para sua análise a minhas querys...

    Um forte abraço.

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

    ResponderExcluir
  7. Ola gostaria, gostaria de ver o resultado do balancete montado para o mysql. Tentei montar um não consegui.
    Realmente para mim é bem difícil, não sou desenvolvedor profissional sou contabilista. Coloco-me a disposição para retribuir a ajuda caso precise.

    ResponderExcluir
    Respostas
    1. Olá Antonio Teles,

      Alguns requisitos mínimos são necessários, entre eles:

      *Modelagem de Dados
      *Desenvolvimento de querys no nível intermediário (no seu caso sabor Mysql)
      *Noções básicas de contabilidade (creio que pela sua formação citada no comentário, já tenha)

      Relativo ao balancete no Mysql, já desenvolvi algo para um cliente em uma modelagem bem especifica, isto é, bem customizada para o banco de dados do cliente, porém usando uma variação do Mysql, no qual seria o MariaDB versão 10.1.14, mesmo com muitas adaptações no código, surtiu excelente desempenho no MariaDB, entretanto no Mysql em se comparando com MariaDB não chegou no mesmo nível, porém foi satisfatório.

      Excluir
  8. Boa tarde,

    Implementei seu exemplo e o resultado ficou show.
    Obrigado por compartilhar seu conhecimento.

    Mas, precisaria que ele mostrasse no balancete contabil o saldo de Receitas - Despesas. Saberia me dizer se existe uma forma de já trazer o saldo após a última linha?

    Muito obrigado!

    Michel

    ResponderExcluir
    Respostas
    1. Olá Michel,

      O motor do cálculo está no plano de contas.

      Isso é simples bastar alterar a estrutura do plano de contas acrescentando mais um nível, tomando o exemplo exemplo supra-citado, segue sugestão de mudança do plano de contas:

      -- antes estava assim

      1 - Receita
      1.1 - Vendas Internas
      1.1.1 - Escola
      1.1.2 - Escritório
      1.2 - Vendas Externas
      1.2.1 - Livro
      1.2.2 - Brinquedos

      2 - Despesas
      2.1 - Fornecedores
      2.1.1 - Nacional
      2.1.2 - Importado
      2.2 - Escritório
      2.2.1 - Materiais de limpeza
      2.2.2 - Materiais de Escritório

      -- depois acrescentando mais um nível totalizador.

      1 - Total Geral
      1.1 - Receita
      1.1.1 - Vendas Internas
      1.1.1.1 - Escola
      1.1.1.2 - Escritório
      1.1.2 - Vendas Externas
      1.1.2.1 - Livro
      1.1.2.2 - Brinquedos
      1.2 - Despesas
      1.2.1 - Fornecedores
      1.2.1.1 - Nacional
      1.2.1.2 - Importado
      1.2.2 - Escritório
      1.2.2.2 - Materiais de Escritório
      1.2.2.1 - Materiais de limpeza

      Excluir