Construir um SQL ou SP. de contas com saldos anter

set term ^ ;
create procedure sp_saldoconta( conta integer )
returns ( saldo numeric(15,2) )
declare variable credito numeric(15,2);
declare variable debito numeric(15,2);
begin
  select coalesce( sum( valor ) , 0 ) from Credito 
  where conta_c = :conta into :credito;

  select coalesce( sum( valor ) , 0 ) from Debito 
  where conta_d = :conta into :debito;
  
  saldo = credito - debito;
  suspend;
end
^
set term ; ^

CREATE PROCEDURE SALDO_CONTA (
    conta varchar(15),
    datainicial date)
returns (
    data date,
    contamovimentada varchar(15),
    saldoanterior numeric(15,2),
    credito numeric(15,2),
    debito numeric(15,2),
    saldoatual numeric(15,2))
as
declare variable valorcredito numeric(15,2);
declare variable valordebito numeric(15,2);
declare variable datafinal date;
begin
  data = datainicial;
  contamovimentada = conta;
  -- datafinal será sempre a data atual do sistema --
  datafinal = current_date;

  -- **************************************************************
  -- estas linhas estão fora do while para que sejam           -- *
  -- executadas apenas 1 vez, aumentando a performance.        -- *
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- *
  -- para sempre obter o saldo anterior correto                -- *
  -- **************************************************************
  select coalesce(sum(valor),0)                                -- *
  from credito                                                 -- *
  where conta_c = :conta and data < :data into :valorcredito;  -- *
                                                               -- *
  select coalesce(sum(valor),0)                                -- *
  from debito                                                  -- *
  where conta_d = :conta and data < :data into :valordebito;   -- *
                                                               -- *
  -- obtenho o saldo anterior subtraindo o débito do crédito   -- *
  saldoanterior = valorcredito - valordebito;                  -- *
  -- **************************************************************

  -- verifico o movimento dia a dia, até o dia anterior à data atual
  while (data < datafinal) do
  begin
    -- somo os créditos do dia
    select coalesce(sum(valor),0)
    from credito
    where conta_c = :conta and data = :data into :credito;

    -- somo os débitos do dia
    select coalesce(sum(valor),0)
    from debito
    where conta_d = :conta and data = :data into :debito;

    saldoatual = saldoanterior + (credito - debito);

    -- se houve algum movimento no dia, exibo o registro
    if ((credito > 0) or (debito > 0))  then
      suspend;

    data = data + 1;
    saldoanterior = saldoanterior + (credito - debito);
  end

  -- somo os créditos da data atual
  select coalesce(sum(valor),0)
  from credito
  where conta_c = :conta and data = :data into :credito;

  -- somo os débitos da data atual
  select coalesce(sum(valor),0)
  from debito
  where conta_d = :conta and data = :data into :debito;

  saldoatual = saldoanterior + (credito - debito);

  -- exibe o registro com o saldo do dia atual
  suspend;
end

data       |conta     |saldo anterior |    debito |    credito |    saldo atual
31/01/06    6                    0,00         0,00        60,00           60,00
28/02/06    6                   60,00         0,00        30,00           90,00
31/03/06    6                   90,00        40,00         0,00           50,00
30/04/06    6                   50,00        60,00         0,00          -10,00
19/01/07    6                  -10,00         0,00         0,00          -10,00

data       |conta     |saldo anterior |    debito |    credito |    saldo atual
31/03/06    6                   90,00       40,00         0,00            50,00
30/04/06    6                   50,00       60,00         0,00           -10,00
19/01/07    6                  -10,00        0,00         0,00           -10,00

SET TERM ^ ;

CREATE PROCEDURE SALDO_TODASCONTA (
    CONTA VARCHAR(15),
    DATAINICIAL DATE)
RETURNS (
    DATA DATE,
    CONTAMOVIMENTADA VARCHAR(15),
    SALDOANTERIOR NUMERIC(15,2),
    CREDITO NUMERIC(15,2),
    DEBITO NUMERIC(15,2),
    DC VARCHAR(1),
    SALDOATUAL NUMERIC(15,2))
AS
DECLARE VARIABLE VALORCREDITO NUMERIC(15,2);
DECLARE VARIABLE VALORDEBITO NUMERIC(15,2);
DECLARE VARIABLE DATAFINAL DATE;
begin
  data = datainicial;
  contamovimentada = conta;
  -- datafinal será sempre a data atual do sistema --
  datafinal = current_date;

  -- **************************************************************
  -- estas linhas estão fora do while para que sejam            -- *
  -- executadas apenas 1 vez, aumentando a performance.        -- *
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- *
  -- para sempre obter o saldo anterior correto                -- *
  -- ***********************************************************************
  select coalesce(sum(valor),0)                                         -- *
  from credito_mestotal                                                 -- *
  where conta_c = :conta and datalcto < :data into :valorcredito;       -- *
                                                                        -- *
  select coalesce(sum(valor),0)                                         -- *
  from debito_mestotal                                                  -- *
  where conta_d = :conta and datalcto < :data into :valordebito;        -- *
                                                                        -- *
  -- obtenho o saldo anterior subtraindo o débito do crédito            -- *
  saldoanterior = valorcredito - valordebito;                           -- *
  -- ***********************************************************************

  -- verifico o movimento dia a dia, até o dia anterior à data atual
  while (data < datafinal) do
  begin
    -- somo os créditos do dia
    select coalesce(sum(valor),0)
    from credito_mestotal
    where conta_c = :conta and datalcto = :data into :credito;

    -- somo os débitos do dia
    select coalesce(sum(valor),0)
    from debito_mestotal
    where conta_d = :conta and datalcto = :data into :debito;

    saldoatual = saldoanterior + (credito - debito);

    -- se o saldo atual for maior que zero
    if (saldoatual > 0) then
       dc = ´D´;
    else
       dc = ´C´;

    -- se houve algum movimento no dia, exibo o registro
    if ((credito > 0) or (debito > 0))  then
      suspend;
      data = data + 1;
    saldoanterior = saldoanterior + (credito - debito);
  end



  -- somo os créditos da data atual
  select coalesce(sum(valor),0)
  from credito_mestotal
    where conta_c = :conta and datalcto = :data into :credito;

  -- somo os débitos da data atual
  select coalesce(sum(valor),0)
  from debito_mestotal
    where conta_d = :conta and datalcto = :data into :debito;

  saldoatual = saldoanterior + (credito - debito);

  -- se o saldo atual for maior que zero
  if (saldoatual > 0) then
     dc = ´D´;
  else
     dc = ´C´;

  -- exibe o registro com o saldo do dia atual
  suspend;
end^

SET TERM ; ^

GRANT SELECT ON CREDITO_MESTOTAL TO PROCEDURE SALDO_TODASCONTA;

GRANT SELECT ON DEBITO_MESTOTAL TO PROCEDURE SALDO_TODASCONTA;

GRANT EXECUTE ON PROCEDURE SALDO_TODASCONTA TO SYSDBA;

data     nivel1 D     nivel2 C            valor 
31/01/2006  1              2              30,00 
31/01/2006  1              1              45,00 
28/02/2006  2              1              12,00

select conta, coalesce(sum(valor),0) from todomesconta
  where datalcto < :data into :valorcredito group by conta;

CREATE PROCEDURE SALDO_GRAU1 (
    DATAINICIAL DATE)
RETURNS (
    DATA DATE,
    SALDOANTERIOR NUMERIC(15,2),
    CREDITO NUMERIC(15,2),
    DEBITO NUMERIC(15,2),
    DC VARCHAR(1),
    NIVELD VARCHAR(15),
    NIVELC VARCHAR(15),
    SALDOATUAL NUMERIC(15,2))
AS
DECLARE VARIABLE VALORCREDITO NUMERIC(15,2);
DECLARE VARIABLE VALORDEBITO NUMERIC(15,2);
DECLARE VARIABLE DATAFINAL DATE;
begin
  data = datainicial;
  --contamovimentada = conta;
  --contamovimentada2= conta2;
  -- datafinal será sempre a data atual do sistema --
  datafinal = current_date;

  -- ***************************************************************
  -- estas linhas estão fora do while para que sejam            -- *
  -- executadas apenas 1 vez, aumentando a performance.         -- *
  -- depois apenas faço a soma dos valores obtidos dia a dia    -- *
  -- para sempre obter o saldo anterior correto                 -- *
  -- ***************************************************************
  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto < :data group by nivel6, nivel1
     into :nivelD, :nivelC, :valordebito do

  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto < :data group by nivel6, nivel1
     into :nivelD, :nivelC, :valorcredito do
   -- obtenho o saldo anterior subtraindo o débito do crédito
  saldoanterior = valordebito - valorcredito;
  -- se o saldo atual for maior que zero
  if (saldoatual > 0) then
     dc = ´D´;
  else
     dc = ´C´;
  -- ***********************************************************************

  -- verifico o movimento dia a dia, até o dia anterior à data atual
  while (data < datafinal) do
  begin
    -- somo os créditos do dia
  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto = :data group by nivel6, nivel1
     into :nivelD, :nivelC, :debito do

  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto = :data group by nivel6, nivel1
     into  :nivelD, :nivelC, :credito do
  -- obtenho o saldo atual
     saldoatual = saldoanterior + (debito - credito);

    -- se houve algum movimento no dia, exibo o registro
    if ((credito > 0) or (debito > 0))  then
      suspend;

    data = data + 1;
    saldoanterior = saldoanterior + (debito - credito);
  end

  -- se o saldo atual for maior que zero
  if (saldoatual > 0) then
     dc = ´D´;
  else
     dc = ´C´;

  -- somo os débitos e créditos da data atual
  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto = :data group by nivel6, nivel1
     into :nivelD, :nivelC, :debito do

  for select nivel6, nivel1, coalesce(sum(valor),0) from todomesconta
     where datalcto = :data group by nivel6, nivel1
     into :nivelD, :nivelC, :credito do

       saldoatual = saldoanterior + (debito - credito);

  -- se o saldo atual for maior que zero
  if (saldoatual > 0) then
     dc = ´D´;
  else
     dc = ´C´;

  -- exibe o registro com o saldo do dia atual
  suspend;
end

with dm.qtgrau1 do
     begin
         close;
         sql.clear;
         sql.add(´select graud, grauc, coalesce(sum(valor),0)as valor from grau1´);
         sql.add(´where (graud between :pgrauini and :pgraufin) or (grauc between :pgrauini and :pgraufin) and datalcto >= :data group by graud, grauc´);
         Parambyname(´pgrauini´).asSTRING := EdtGrauD.text;
         Parambyname(´pgraufin´).asSTRING := EdtGrauC.text;
         Parambyname(´DATA´).asDATE := StrToDATE(MASKEDIT2.text);
         open;
     end;
end;

with dm.datasetgd1 do
     begin
         close;
         sql.clear;
         sql.add(´select grau1d, coalesce(sum(valor),0)as valordebito from tbgrau1D´);
         sql.add(´where (grau1d between :pgrauini and :pgraufin) and datalcto >= :data group by grau1d´);
         Parambyname(´pgrauini´).asSTRING := EdtContaIni.text;
         Parambyname(´pgraufin´).asSTRING := EdtContaFin.text;
         Parambyname(´data´).asDATE := StrToDATE(MaskData.text);
         open;
     end;

with dm.datasetgc1 do
     begin
         close;
         sql.clear;
         sql.add(´select grau1c, coalesce(sum(valor),0)as valorcredito from tbgrau1C´);
         sql.add(´where (grau1c between :pgrauini and :pgraufin) and datalcto >= :data group by grau1c´);
         Parambyname(´pgrauini´).asSTRING := EdtContaIni.text;
         Parambyname(´pgraufin´).asSTRING := EdtContaFin.text;
         Parambyname(´data´).asDATE := StrToDATE(MaskData.text);
         open;
     end;

with dm.datasetgd2 do
     begin
         close;
         sql.clear;
         sql.add(´select grau2d, coalesce(sum(valor),0)as valordebito from tbgrau2D´);
         sql.add(´where (grau2d between :pgrauini and :pgraufin) and datalcto >= :data group by grau2d´);
         Parambyname(´pgrauini´).asSTRING := EdtContaIni.text;
         Parambyname(´pgraufin´).asSTRING := EdtContaFin.text;
         Parambyname(´data´).asDATE := StrToDATE(MaskData.text);
         open;
     end;

with dm.datasetgc2 do
     begin
         close;
         sql.clear;
         sql.add(´select grau2c, coalesce(sum(valor),0)as valorcredito from tbgrau2C´);
         sql.add(´where (grau2c between :pgrauini and :pgraufin) and datalcto >= :data group by grau2c´);
         Parambyname(´pgrauini´).asSTRING := EdtContaIni.text;
         Parambyname(´pgraufin´).asSTRING := EdtContaFin.text;
         Parambyname(´data´).asDATE := StrToDATE(MaskData.text);
         open;
     end;

with dm.qtgrau1 do
     begin
         close;
         sql.clear;
         sql.add(´select graud, n_Nivel, coalesce(sum(valor),0)as valordebito from tgrau1_D´);
         sql.add(´where (graud between :pgrauini and :pgraufin) and datalcto >= :data group by graud, n_Nivel´);
         sql.add(´union´);
         sql.add(´select grauc, n_Nivel, coalesce(sum(valor),0)as valorcredito from tgrau1_C´);
         sql.add(´where (grauc between :pgrauini and :pgraufin) and datalcto >= :data group by grauc, n_Nivel´);
         sql.add(´union´);
         sql.add(´select grau2d, n_Nivel, coalesce(sum(valor),0)as valordebito from tgrau2_D´);
         sql.add(´where (grau2d between :pgrauini and :pgraufin) and datalcto >= :data group by grau2d, n_Nivel´);
         sql.add(´union´);
         sql.add(´select grau2c, n_Nivel, coalesce(sum(valor),0)as valorcredito from tgrau2_C´);
         sql.add(´where (grau2c between :pgrauini and :pgraufin) and datalcto >= :data group by grau2c, n_Nivel´);
         Parambyname(´pgrauini´).asSTRING := EdtConta.text;
         Parambyname(´pgraufin´).asSTRING := Edit1.text;
         Parambyname(´DATA´).asDATE := StrToDATE(MASKEDIT2.text);
         open;
     end;
end;

begin 
  /* Procedure Text */ 
  data = datainicial; 
  -- datafinal será sempre a data atual do sistema -- 
  datafinal = current_date; 
  -- INICIANDO OS SELECTS DAS 4 TABELAS-- 
  select coalesce(sum(valor),0)as valordebito from tgrau1_d 
     where datalcto < :data into :valordebito; 

  select coalesce(sum(valor),0)as valorcredito from tgrau1_c 
     where datalcto < :data into :valorcredito; 

  select coalesce(sum(valor),0)as valordebito from tgrau2_d 
     where datalcto < :data into :valordebito; 

  select coalesce(sum(valor),0)as valorcredito from tgrau2_c 
     where datalcto < :data into :valorcredito; 
  -- obtenho o saldo anterior subtraindo o débito do crédito 
  saldoanterior = valorcredito - valordebito; 
          
   -- verifico o movimento dia a dia, até o dia anterior à data atual 
  while (data <datafinal> 0) or (debito > 0))  then 
      suspend; 

  data = data + 1; 
  saldoanterior = saldoanterior + (credito - debito); 
  end 

  -- somo os debitos e créditos da data atual 
  select coalesce(sum(valor),0)as valordebito from tgrau1_d 
     where datalcto = :data into :debito; 

  select coalesce(sum(valor),0)as valorcredito from tgrau1_c 
     where datalcto = :data into :credito; 

  select coalesce(sum(valor),0)as valordebito from tgrau2_d 
     where datalcto = :data into :debito; 

  select coalesce(sum(valor),0)as valorcredito from tgrau2_c 
     where datalcto = :data into :credito; 

  saldoatual = saldoanterior + (credito - debito); 

  suspend; 
end

with SP do
     begin
         close;
         sql.clear;
         sql.add(´select * from saldo_contames(:conta, :DATAINICIAL´);
         Parambyname(´DATAINICIAL´).AsDate := StrToDate(maskedit2.text);
         Parambyname(´CONTA´).AsString := EdtContaI.text;
         open;
     end;

select 
d.graud, d.n_nivel, c.grauc, c.n_nivel, d2.grau2d, c2.grau2c, 
sum(d.valor)as valordebito, 
sum(c.valor)as valorcredito 
from
tgrau1_d d
left outer join
tgrau1_c c on d.sequencia = c.sequencia,
tgrau2_d d2, 
tgrau2_c c2 
group by d.graud, c.grauc,d2.grau2d, c2.grau2c,d.n_nivel, c.n_nivel

with SP do
     begin
         close;
         sql.clear;
         sql.add(´select * from saldo_contames(:conta, :DATAINICIAL´);
         Parambyname(´DATAINICIAL´).AsDate := StrToDate(maskedit2.text);
         Parambyname(´CONTA´).AsString := EdtContaI.text;
         open;
     end;

select 
d.graud, d.n_nivel, c.grauc, c.n_nivel, d2.grau2d, c2.grau2c, 
sum(d.valor)as valordebito, 
sum(c.valor)as valorcredito 
from
tgrau1_d d
left outer join
tgrau1_c c on d.sequencia = c.sequencia,
tgrau2_d d2, 
tgrau2_c c2 
group by d.graud, c.grauc,d2.grau2d, c2.grau2c,d.n_nivel, c.n_nivel

with SP do
begin
  close;
  sql.clear;
  sql.add(´select * from saldo_contames(:conta, :DATAINICIAL)´); // aqui faltava um parêntesis
  Parambyname(´DATAINICIAL´).AsDate := StrToDate(maskedit2.text);
  Parambyname(´CONTA´).AsString := EdtContaI.text;
  open;
end;

CREATE PROCEDURE SPTOTALGRAU (
    CONTAI VARCHAR(15),
    DATAINICIAL DATE)
RETURNS (
    DATA DATE,
    SALDOANTERIOR NUMERIC(15,2),
    DEBITO NUMERIC(15,2),
    CREDITO NUMERIC(15,2),
    SALDOATUAL NUMERIC(15,2),
    DC VARCHAR(1),
    CONTAMOVIMENTADA VARCHAR(15))
AS
DECLARE VARIABLE VALORDEBITO NUMERIC(15,2);
DECLARE VARIABLE VALORCREDITO NUMERIC(15,2);
DECLARE VARIABLE DATAFINAL DATE;
begin
  /* Procedure Text */
  data = datainicial; 
  contamovimentada = contaI;
  -- datafinal será sempre a data atual do sistema -- 
  datafinal = current_date; 

  -- **************************************************************
  -- estas linhas estão fora do while para que sejam           -- *
  -- executadas apenas 1 vez, aumentando a performance.        -- *
  -- depois apenas faço a soma dos valores obtidos dia a dia   -- *
  -- para sempre obter o saldo anterior correto                -- *
  -- **************************************************************
  select coalesce(sum(valor),0)as valordebito from tgrau1_d
     where (cast(graud as integer) between :contai and :contai)  and datalcto < :data into :valordebito;

  select coalesce(sum(valor),0)as valorcredito from tgrau1_c
     where (cast(grauc as integer) between :contai and :contai)  and datalcto < :data into :valorcredito;

  select coalesce(sum(valor),0)as valordebito from tgrau2_d
     where (cast(grau2d as integer) between :contai and :contai) and datalcto < :data into :valordebito;

  select coalesce(sum(valor),0)as valorcredito from tgrau2_c
     where (cast(grau2c as integer) between :contai and :contai) and datalcto < :data into :valorcredito;

  -- obtenho o saldo anterior subtraindo o débito do crédito            -- *
  saldoanterior = valorcredito - valordebito;                           -- *
  -- ***********************************************************************

  -- verifico o movimento dia a dia, até o dia anterior à data atual
  while (data < datafinal) do
  begin
  -- somo os créditos do dia
  select coalesce(sum(valor),0)as valordebito from tgrau1_d
     where (cast(graud as integer) between :contai and :contai)  and datalcto = :data into :debito;

  select coalesce(sum(valor),0)as valorcredito from tgrau1_c 
     where (cast(grauc as integer) between :contai and :contai)  and datalcto = :data into :credito;

  select coalesce(sum(valor),0)as valordebito from tgrau2_d 
     where (cast(grau2d as integer) between :contai and :contai) and datalcto = :data into :debito;

  select coalesce(sum(valor),0)as valorcredito from tgrau2_c 
     where (cast(grau2c as integer) between :contai and :contai) and datalcto = :data into :credito;

    saldoatual = saldoanterior + (credito - debito);

    -- se houve algum movimento no dia, exibo o registro
    if ((credito > 0) or (debito > 0))  then
      suspend;

    data = data + 1;
    saldoanterior = saldoanterior + (credito - debito);
  end

  -- somo os créditos da data atual
    select coalesce(sum(valor),0)as valordebito from tgrau1_d
     where (cast(graud as integer) between :contai and :contai)  and datalcto = :data into :debito;

  select coalesce(sum(valor),0)as valorcredito from tgrau1_c 
     where (cast(grauc as integer) between :contai and :contai)  and datalcto = :data into :credito;

  select coalesce(sum(valor),0)as valordebito from tgrau2_d 
     where (cast(grau2d as integer) between :contai and :contai) and datalcto = :data into :debito;

  select coalesce(sum(valor),0)as valorcredito from tgrau2_c 
     where (cast(grau2c as integer) between :contai and :contai) and datalcto = :data into :credito;

  saldoatual = saldoanterior + (credito - debito);

  -- se o saldo atual for maior que zero
  if (saldoatual > 0) then
     dc = ´D´;
  else
     dc = ´C´;

  -- exibe o registro com o saldo do dia atual
  suspend;
end

select d.N_Nivel, c.N_Nivel, d2.n_Nivel, c2.n_Nivel,
sum(coalesce(d.valor,0))as valordebito, 
sum(coalesce(c.valorcredito,0))as valorcredito,
sum(coalesce(d.VALOR,0)) - sum(coalesce(c.VALORCREDITO,0)) as Saldo 
from
tgrau1_d d
left outer join
tgrau1_c c on c.n_Nivel = d.n_Nivel,
tgrau2_d d2, 
tgrau2_c c2 
where d.N_Nivel = d.N_Nivel
group by 1, 2, 3, 4

select distinct(a.n_nivel) "Conta Debito/Credito",
sum(coalesce(a.valor,0))"Valor Débito", 
sum(coalesce(b.valor,0))"Valor Crédito",
sum(coalesce(a.VALOR,0)) - sum(coalesce(b.VALOR,0)) "Valor Saldo" 
from tgrau1_d a
full outer join
tgrau1_c b on b.n_nivel = a.n_nivel
where a.n_nivel  = a.n_nivel and a.ano = a.ano
group by 1