Select Recursivo

[viniciosacampos]

Boa Tarde,

Estou com um problema no desenvolvimento de uma query e não estou conseguindo uma solução prática e simples.

Selecionar tudo

create table tb_saldo
(
  dt_atual   date,
  cd_cliente number(7),
  vl_saldo   number
);

Selecionar tudo

insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('26-07-2007', 'dd-mm-yyyy'), 112703, -10);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('27-07-2007', 'dd-mm-yyyy'), 112703, -20);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('30-07-2007', 'dd-mm-yyyy'), 112703, 10);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('31-07-2007', 'dd-mm-yyyy'), 112703, -20);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('01-08-2007', 'dd-mm-yyyy'), 112703, -40);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('02-08-2007', 'dd-mm-yyyy'), 112703, -50);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('03-08-2007', 'dd-mm-yyyy'), 112703, -60);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('06-08-2007', 'dd-mm-yyyy'), 112703, -10);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('07-08-2007', 'dd-mm-yyyy'), 112703, 10);
insert into tb_saldo (dt_atual, cd_cliente, vl_saldo) values (to_date('09-08-2007', 'dd-mm-yyyy'), 112703, 20);

Queria fazer um select por um intervalo de data, retornasse os dias devedores recorrente do cliente.

Selecionar tudo

Cliente 112703 dia '26-07-2007' está devedor recorrrente a 1 dia.
Cliente 112703 dia '27-07-2007' está devedor recorrrente a 2 dias.
Cliente 112703 dia '30-07-2007' não está devedor;
Cliente 112703 dia '31-07-2007' está devedor recorrrente a 1 dia.
Cliente 112703 dia '01-08-2007' está devedor recorrrente a 2 dias.
Cliente 112703 dia '02-08-2007' está devedor recorrrente a 3 dia.
Cliente 112703 dia '03-08-2007' está devedor recorrrente a 4 dias.
Cliente 112703 dia '06-08-2007' está devedor recorrrente a 5 dias.
Cliente 112703 dia '07-08-2007' não está devedor;
Cliente 112703 dia '09-08-2007' não está devedor;

Alguém tem alguma solução por favor.

[FernandesRE]

Olá Vinício!

Montei uma possível solução para o seu problema.

Como a função COUNT não conta valores nulos, fiz umas jogadas para:

1 - Gerar um numero para cada grupo de tempo que o cliente ficou devedor

• Usei a função SIGN para saber se o valor era ou não negativo

• Depois com um DECODE, transformei os valores negativos em NULL

• Em seguida fui contando quantos valores positivos eu tinha, ordenando por data

• Cada mudança na quantidade de valores positivos, considerei uma mudança de grupo

2 - Com os grupos prontos ficou simples.

• contei agora quantos valores negativos (DECODE inverso ao anterior) existem em cada grupo

• Com esse segundo COUNT, você tem quantos dias o cliente está devedor

Agora você pode montar o seu texto concatenando os dados

EX:

Selecionar tudo

Cliente 112703 dia '02-08-2007' está devedor recorrrente a 3 dia.

Selecionar tudo

WITH grupos AS
  (SELECT cd_cliente,
    DT_ATUAL,
    vl_saldo,
    COUNT(DECODE(SIGN(VL_SALDO),-1,NULL,1)) OVER (partition BY CD_CLIENTE ORDER BY DT_ATUAL )grupos
  FROM TB_SALDO
  )
SELECT cd_cliente,
  DT_ATUAL,
  vl_saldo,
  grupos,
  COUNT (DECODE(SIGN(VL_SALDO),-1,1,NULL)) over (partition BY cd_cliente, grupos) qtdDias
FROM grupos
ORDER BY cd_cliente,
  dt_atual

Espero que ajude.
[]s

[viniciosacampos]

Obrigado..

Foi de grande utilidade.

Fiz somente somente uma alteração para ordenar os dias devedores com a função row_number.

Valeu.

Selecionar tudo

with grupos as
 (select cd_cliente,
         dt_atual,
         vl_saldo,
         count(decode(sign(vl_saldo), -1, null, 1)) over(partition by cd_cliente order by dt_atual) grupos
    from tb_saldo)
select cd_cliente,
       dt_atual,
       vl_saldo,
       grupos,
       count(decode(sign(vl_saldo), -1, 1, null)) over(partition by cd_cliente, grupos) qtddias,
       row_number() over(partition by cd_cliente, decode(sign(vl_saldo), -1, grupos, null) order by cd_cliente, dt_atual) nr_ordem
  from grupos
 order by cd_cliente,
          dt_atual