'Um código bem básico para uma estrutura de três níveis escrito em 'VB para o Access 2010 Option Compare Database Sub centro_custo() ' funciona para uma estrutura de contas em 3 niveis Dim rs1 As Recordset Dim rs2 As Recordset Dim soma_n1, soma_n2, soma_n3 As Double Dim tamanho_conta As Integer Dim strSQL As String ' lista todas as contas cadastradas em centro_custo Set rs1 = CurrentDb.OpenRecordset("SELECT id_centro_custo, descricao, tipo_conta FROM centro_custo ORDER BY id_centro_custo DESC;") Do While Not rs1.EOF ' contas nivel 3 ' todas contas analiticas, nessa estrutura as contas tem o nivel 3 If (rs1("tipo_conta") = "A") Then ' processa o nivel 3 na query em strSQL strSQL = "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 WHERE cc.id_centro_custo = '" & rs1("id_centro_custo") & "' GROUP BY cc.id_centro_custo, cc.descricao;" Set rs2 = CurrentDb.OpenRecordset(strSQL) If Not rs2.EOF Then soma_n3 = rs2("total_conta") Else soma_n3 = 0 End If soma_n2 = soma_n2 + soma_n3 Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n3 Else tamanho_conta = Len(rs1("id_centro_custo")) ' contas nivel 2 If tamanho_conta = 3 Then Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n2 soma_n1 = soma_n1 + soma_n2 soma_n2 = 0 Else ' contas nivel 1 If tamanho_conta = 1 Then Debug.Print rs1("id_centro_custo") & " - " & rs1("descricao") & " - " & rs1("tipo_conta") & " - " & soma_n1 soma_n1 = 0 End If End If End If ' avanca proximo registro rs1.MoveNext Loop End Sub ' Informo ainda que os níveis podem ser configurados via matrizes ' e código desenvolvido acima é procedural ' Recomendo definir uma mascara do plano de contasResultado do Código em VBA Acima Descrito Mais uma vez espero ter ajudado! APdSJC!
DBA, MySql, Oracle, Postgres, SQL Server, DB2, BI, TI, Business Intelligence, Data Science, Machine Learning, IoT, Arduino
quinta-feira, 26 de dezembro de 2013
Código, Algoritmo de um Plano de Contas escrito em VB
Complementando um artigo descrito em http://emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html
Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo, apenas exponho um simples código para elucidar como deveria ser feito, isto é, como seria implementado um algoritmo para totalizar um plano de contas em uma linguagem de programação, a exemplo aqui do VBA, em casos que não é possível usar uma query ou um SGBD sem um suporte mais abrangente ao SQL a exemplo do ACCESS 2010.
sexta-feira, 29 de novembro de 2013
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.
Segue a dica para trabalhar com SQL Fiddle.
Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/
Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.
Mais uma vez espero ter ajudado.
APdSJC!
quinta-feira, 1 de agosto de 2013
Converter várias linhas em uma única linha no SQL Server
Como converter várias linhas em uma única linha no SQL Server, separando por ponto e virgula ";".
USE tempdb; -- DROP TABLE pessoa_fisica; CREATE TABLE pessoa_fisica ( id_pessoa_fisica int IDENTITY (1,1) PRIMARY KEY , nome varchar(100) NOT NULL , sexo varchar(1) NOT NULL ); -- DROP TABLE email; CREATE TABLE email ( id_email int IDENTITY (1,1) PRIMARY KEY , email varchar(100) NOT NULL , id_pessoa_fisica int NOT NULL REFERENCES pessoa_fisica(id_pessoa_fisica) ); -- inserindo na tabela pessoa_fisica INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Maria','F'); INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ieda','F'); INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Ilma','F'); INSERT INTO pessoa_fisica (nome, sexo) VALUES ('Felipe','F'); -- listando todas as pessoas fisicas cadastradas SELECT * FROM pessoa_fisica; -- inserindo na tabela email -- inserindo emails de Maria INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria@exemplo.com', 1); INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.rosa@exemplo.com', 1); INSERT INTO email (email, id_pessoa_fisica) VALUES ('maria.silva@exemplo.com', 1); -- inserindo emails de Ieda INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda@exemplo.com', 2); INSERT INTO email (email, id_pessoa_fisica) VALUES ('ieda.lima@exemplo.com', 2); -- inserindo emails de Felipe INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe@exemplo.com', 4); INSERT INTO email (email, id_pessoa_fisica) VALUES ('felipe.santos@exemplo.com', 4); -- listando todos os emails cadastrados SELECT * FROM email; -- listando emails de Maria -- listando emails de Maria em várias linhas -- exemplo 1.1 SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1; -- listando emails de Maria em uma única linha -- exemplo 1.2 SELECT stuff( (SELECT email + '; ' FROM email WHERE id_pessoa_fisica = 1 FOR XML PATH('')),1,0,''); -- listando todos os emails cadastrados com todas as pessoas cadastradas, -- considerando vários emails em uma unica linha. -- exemplo 2 SELECT pessoa_fisica.nome , stuff ( ( SELECT email.email + '; ' FROM email WHERE email.id_pessoa_fisica = pessoa_fisica.id_pessoa_fisica FOR XML PATH('') ),1,0,'' ) AS emails FROM pessoa_fisica ;Mais uma vez espero ter ajudado ...
quarta-feira, 24 de julho de 2013
Remover caracteres especiais em campos do Postgres
Function scalar para Postgres, com objetivo de remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.
Fica aqui o compartilhamento do conhecimento.
Abraços.
-- -- Nome Artefato/Programa..: usf_rm_special_chars.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- ........................: O Peregrino / http://emersonhermann.blogspot.com -- Data Inicio ............: 09/10/2008 -- Data Atual..............: 24/07/2012 -- Versao..................: 0.02 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x/9.x -- Kernel..................: Nao informado! -- Finalidade..............: store function para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS usf_rm_special_chars (TEXT); CREATE OR REPLACE FUNCTION usf_rm_special_chars (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; BEGIN -- Tabela ASCII -- Caracteres não imprimiveis, 33 ao total, menos o NUL ficam 32 -- hex d abr ctl descricao -- 00 0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata stextonovo := translate(stexto, chr(1),''); -- 01 1 SOH ^A Start of Header - Início do cabeçalho stextonovo := translate(stextonovo, chr(2),''); -- 02 2 STX ^B Start of Text - Início do texto stextonovo := translate(stextonovo, chr(3),''); -- 03 3 ETX ^C End of Text - Fim do texto stextonovo := translate(stextonovo, chr(4),''); -- 04 4 EOT ^D End of Tape - Fim de fita stextonovo := translate(stextonovo, chr(5),''); -- 05 5 ENQ ^E Enquire - Interroga identidade do terminal stextonovo := translate(stextonovo, chr(6),''); -- 06 6 ACK ^F Acknowledge - Reconhecimento stextonovo := translate(stextonovo, chr(7),''); -- 07 7 BEL ^G Bell - Campainha stextonovo := translate(stextonovo, chr(8),''); -- 08 8 BS ^H Back-space - Espaço atrás stextonovo := translate(stextonovo, chr(9),''); -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal stextonovo := translate(stextonovo, chr(10),''); -- 0A 10 LF ^J Line-Feed - Alimenta linha stextonovo := translate(stextonovo, chr(11),''); -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical stextonovo := translate(stextonovo, chr(12),''); -- 0C 12 FF ^L Form-Feed - Alimenta formulário stextonovo := translate(stextonovo, chr(13),''); -- 0D 13 CR ^M Carriage-Return - Retorno do carro (enter) stextonovo := translate(stextonovo, chr(14),''); -- 0E 14 SO ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.) stextonovo := translate(stextonovo, chr(15),''); -- 0F 15 SI ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.) stextonovo := translate(stextonovo, chr(16),''); -- 10 16 DLE ^P Data-Link Escape stextonovo := translate(stextonovo, chr(17),''); -- 11 17 DC1 ^Q Device-Control 1 stextonovo := translate(stextonovo, chr(18),''); -- 12 18 DC2 ^R Device-Control 2 stextonovo := translate(stextonovo, chr(19),''); -- 13 19 DC3 ^S Device-Control 3 stextonovo := translate(stextonovo, chr(20),''); -- 14 20 DC4 ^T Device-Control 4 stextonovo := translate(stextonovo, chr(21),''); -- 15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento stextonovo := translate(stextonovo, chr(22),''); -- 16 22 SYN ^V Synchronous Idle stextonovo := translate(stextonovo, chr(23),''); -- 17 23 vETB^W End-of-Transmission Block stextonovo := translate(stextonovo, chr(24),''); -- 18 24 CAN ^X Cancel stextonovo := translate(stextonovo, chr(25),''); -- 19 25 EM ^Y End-Of-Medium stextonovo := translate(stextonovo, chr(26),''); -- 1A 26 SUB ^Z Substitute stextonovo := translate(stextonovo, chr(27),''); -- 1B 27 ESC ^[ Escape stextonovo := translate(stextonovo, chr(28),''); -- 1C 28 FS ^\ File Separator stextonovo := translate(stextonovo, chr(29),''); -- 1D 29 GS ^] Group Separator stextonovo := translate(stextonovo, chr(30),''); -- 1E 30 RS ^^ Record Separator stextonovo := translate(stextonovo, chr(31),''); -- 1F 31 US ^_ Unit Separator stextonovo := translate(stextonovo, chr(127),''); -- 7F127 DEL ^? Delete RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;Test 1 Test 2
-- string com caracteres especiais, formato 2 SELECT 'SEM STRESS... ' || chr(1) || 'TECLA ENTER: ' || chr(13) || ' TECLA ESC: ' || chr(27) || ' TESTE OK ' AS test3; -- string sem caracteres espciais usando a function, formato 2 SELECT usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test4; -- verificando tamanho da string, 53 caracteres, incluindo 3 especiais SELECT length('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test5; -- verificando tamanho da string, 50 caracteres, removido 3 especiais SELECT length(usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ')) AS test6;
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 , 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!
Marcadores:
Algoritmos,
Oracle,
Postgres,
SQL,
SQL Server
Assinar:
Postagens (Atom)