Somar As subcontas - Nas Contas
04/06/2010
0
Bom dia Pessoal
Estou com o seguinte problema....
meu script apresenta a os dados assim?
Conta Descricao Janeiro Fevereiro....ate dezembro....
1 Ativo
12 Circulante
123 Bancos
12399 Brasil 15.000 23.000
12398 Bradesco 35.000 6.000
Eu preciso que ele some esses valores nas contas principais... por exemplo.... todo mundo que comece com a conta numero 1...... somar tudo na coluna de ativos.... somar tudo que comece com 12... somar tudo na frente do ~circulante~ assim por diante... mes a mes....
Criei um banco exemplo pra facilitar a ajuda, segue:
SQL> create table clanca (
2 data date,
3 credito varchar2(11),
4 debito varchar2(11),
5 valor number(10,2));
SQL> create table cConta (Codconta varchar2(11);
2 descricao varchar2(40);
SQL> create table Saldo (Codconta varchar2(11);
2 descricao varchar2(40);
dez number(10,2); --Insert cLanca
insert into clanca (data, credito, debito, valor)
values (to_date('05/01/2010', 'DD/MM/YYYY'), 12345678909, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date('06/01/2010', 'DD/MM/YYYY'), 12345678909, null, 4034);
insert into clanca (data, credito, debito, valor)
values (to_date('05/01/2010', 'DD/MM/YYYY'), 12345678909, null, 2066);
insert into clanca (data, credito, debito, valor)
values (to_date('06/01/2010', 'DD/MM/YYYY'), 12345678909, null, 4450);
insert into clanca (data, credito, debito, valor)
values (to_date('10/01/2010', 'DD/MM/YYYY'), 12345678911, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date('23/01/2010', 'DD/MM/YYYY'), 12345678911, null, 4000);
--Insert Saldo
insert into Saldo (CodConta, dez)
values ('12345678909',234.77);
insert into Saldo (CodConta, dez)
values ('12345678911',500);
Select
c.codConta As CODCONTAS
,C.Descricao As DESCRICAO
, (CASE WHEN
length(c.codConta) <= 4 THEN C.Descricao ELSE c.Descricao END) As DESCRICAO
, (CASE WHEN &mes >= 01 THEN (Sum(case when To_Char(L.Data, 'mm') = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0))END) Janeiro
, (CASE WHEN &mes >= 02 THEN (Sum(case when To_Char(L.Data, 'mm') <= '02' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '02' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Fevereiro
, (CASE WHEN &mes >= 03 THEN (Sum(case when To_Char(L.Data, 'mm') <= '03' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '03' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Marco
, (CASE WHEN &mes >= 04 THEN (Sum(case when To_Char(L.Data, 'mm') <= '04' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '04' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Abril
, (CASE WHEN &mes >= 05 THEN (Sum(case when To_Char(L.Data, 'mm') <= '05' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '05' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Maio
, (CASE WHEN &mes >= 06 THEN (Sum(case when To_Char(L.Data, 'mm') <= '06' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '06' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Junho
, (CASE WHEN &mes >= 07 THEN (Sum(case when To_Char(L.Data, 'mm') <= '07' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '07' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Julho
, (CASE WHEN &mes >= 08 THEN (Sum(case when To_Char(L.Data, 'mm') <= '08' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '08' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Agosto
, (CASE WHEN &Mes >= 09 THEN (Sum(case when To_Char(L.Data, 'mm') <= '09' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '09' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Setembro
, (CASE WHEN &mes >= 10 THEN (Sum(case when To_Char(L.Data, 'mm') <= '10' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '10' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Outubro
, (CASE WHEN &mes >= 11 THEN (Sum(case when To_Char(L.Data, 'mm') <= '11' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '11' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Novembro
, (CASE WHEN &mes >= 12 THEN (Sum(case when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Dezembro
From cLanca L right join cConta C
ON L.CodColigada = C.CodColigada
AND (L.Credito = C.CodConta
OR L.Debito = C.CodConta)
AND To_Char(L.Data,'yy') = '10'
left join Saldo O
ON O.CodConta = C.CodConta
where
Substr(C.Codconta,1,1) = '1'
GROUP BY C.CodConta,
C.Descricao, O.dez
ORDER BY C.codConta Desde j[a obrigado pela ajuda.
2 data date,
3 credito varchar2(11),
4 debito varchar2(11),
5 valor number(10,2));
SQL> create table cConta (Codconta varchar2(11);
2 descricao varchar2(40);
SQL> create table Saldo (Codconta varchar2(11);
2 descricao varchar2(40);
dez number(10,2); --Insert cLanca
insert into clanca (data, credito, debito, valor)
values (to_date('05/01/2010', 'DD/MM/YYYY'), 12345678909, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date('06/01/2010', 'DD/MM/YYYY'), 12345678909, null, 4034);
insert into clanca (data, credito, debito, valor)
values (to_date('05/01/2010', 'DD/MM/YYYY'), 12345678909, null, 2066);
insert into clanca (data, credito, debito, valor)
values (to_date('06/01/2010', 'DD/MM/YYYY'), 12345678909, null, 4450);
insert into clanca (data, credito, debito, valor)
values (to_date('10/01/2010', 'DD/MM/YYYY'), 12345678911, null, 2000);
insert into clanca (data, credito, debito, valor)
values (to_date('23/01/2010', 'DD/MM/YYYY'), 12345678911, null, 4000);
--Insert Saldo
insert into Saldo (CodConta, dez)
values ('12345678909',234.77);
insert into Saldo (CodConta, dez)
values ('12345678911',500);
Select
c.codConta As CODCONTAS
,C.Descricao As DESCRICAO
, (CASE WHEN
length(c.codConta) <= 4 THEN C.Descricao ELSE c.Descricao END) As DESCRICAO
, (CASE WHEN &mes >= 01 THEN (Sum(case when To_Char(L.Data, 'mm') = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') = '01' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0))END) Janeiro
, (CASE WHEN &mes >= 02 THEN (Sum(case when To_Char(L.Data, 'mm') <= '02' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '02' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Fevereiro
, (CASE WHEN &mes >= 03 THEN (Sum(case when To_Char(L.Data, 'mm') <= '03' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '03' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Marco
, (CASE WHEN &mes >= 04 THEN (Sum(case when To_Char(L.Data, 'mm') <= '04' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '04' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Abril
, (CASE WHEN &mes >= 05 THEN (Sum(case when To_Char(L.Data, 'mm') <= '05' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '05' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Maio
, (CASE WHEN &mes >= 06 THEN (Sum(case when To_Char(L.Data, 'mm') <= '06' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '06' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Junho
, (CASE WHEN &mes >= 07 THEN (Sum(case when To_Char(L.Data, 'mm') <= '07' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '07' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Julho
, (CASE WHEN &mes >= 08 THEN (Sum(case when To_Char(L.Data, 'mm') <= '08' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '08' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Agosto
, (CASE WHEN &Mes >= 09 THEN (Sum(case when To_Char(L.Data, 'mm') <= '09' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '09' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Setembro
, (CASE WHEN &mes >= 10 THEN (Sum(case when To_Char(L.Data, 'mm') <= '10' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '10' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Outubro
, (CASE WHEN &mes >= 11 THEN (Sum(case when To_Char(L.Data, 'mm') <= '11' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '11' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Novembro
, (CASE WHEN &mes >= 12 THEN (Sum(case when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Debito THEN -L.Valor
when To_Char(L.Data, 'mm') <= '12' AND nvl(L.Valor,0) IS NOT NULL AND c.CodConta = L.Credito THEN L.Valor end)+Nvl(O.dez,0)) END) Dezembro
From cLanca L right join cConta C
ON L.CodColigada = C.CodColigada
AND (L.Credito = C.CodConta
OR L.Debito = C.CodConta)
AND To_Char(L.Data,'yy') = '10'
left join Saldo O
ON O.CodConta = C.CodConta
where
Substr(C.Codconta,1,1) = '1'
GROUP BY C.CodConta,
C.Descricao, O.dez
ORDER BY C.codConta Desde j[a obrigado pela ajuda.
Anderson Oliveira....
Curtir tópico
+ 0
Responder
Clique aqui para fazer login e interagir na Comunidade :)