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
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) 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 pla.id_plano_conta
, lan.dt_lancamento
, 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
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) 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
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) 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 pla.id_plano_conta
, lan.dt_lancamento
, 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
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) 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!
