SQL com variável

[brunoducatti]

Bom dia!

Preciso criar uma variável que tenha todos os dias desde o primeiro dia do ano anterior (01/01/2017) até o dia atual.

Com base nestes dias, preciso executar o script abaixo para cada um dos dias do período citado. Ou seja, onde consta no SQL abaixo "sysdate" eu preciso que execute o SQL para cada dia do período citado.

Tem como fazer isso no Qlikview?

Obrigado!

SELECT                                                                                                                                                          

EM.APELIDO,                                                                                                                                                     

SALDO_ANTERIOR,                                                                                                                                                 

TOTAL_ENTRADA,                                                                                                                                                  

TOTAL_SAIDA,                                                                                                                                                    

TIPO_CONTA,                                                                                                                                                     

NVL(SALDO_ANTERIOR,0) + NVL(TOTAL_ENTRADA,0) - NVL(TOTAL_SAIDA,0) AS SALDO,                                                                                     

     NVL(SALDO_ANTERIOR,0) + NVL(TOTAL_ENTRADA,0) - NVL(TOTAL_SAIDA,0) + NVL(CC.SALDO_LIMITE,0) AS SALDO_DISP                                                    

FROM XCORRENTE CC, XLOJA EM,                                                                                                                             

     (SELECT COD_LOJA AS EMPRESA,                                                                                                                             

             COD_CONTA_CORRENTE AS CONTA,                                                                                                                        

             SUM(DECODE(sign(TO_DATE(TO_CHAR(sysdate,'DD-MM-YYYY'),'DD-MM-YYYY') - DATA_FIM),1,VALOR_CREDITO-VALOR_DEBITO,0)) AS SALDO_ANTERIOR,            

             SUM(DECODE(DATA_FIM,sysdate,VALOR_CREDITO,0)) AS TOTAL_ENTRADA,                                                                                    

             SUM(DECODE(DATA_FIM,sysdate,VALOR_DEBITO,0))  AS TOTAL_SAIDA                                                                                       

      FROM XSALDO                                                                                                                                          

      WHERE DATA_FIM <= sysdate                                                                                                                              

      GROUP BY COD_LOJA, COD_CONTA_CORRENTE)                                                                                                                  

WHERE CC.COD_LOJA = EMPRESA(+)                                                                                                                                

  AND CC.COD_CONTA_CORRENTE = CONTA(+)                                                                                                                           

  AND CC.COD_LOJA = EM.COD_LOJA                                                                                                                            

  AND CC.ATIVA = 'S'

[nicolett_yuri]

Bruno, é só criar a variável (no script)

LET vDataInicio = Date(YearStart(AddYears(Today(),-1)), 'DD/MM/YYYY');

LET vDataFim = Today();

Na sua consulta, qual a coluna de data de início?

Na consulta, ficaria:

Data >= '$(vDataInicio)'

[nicolett_yuri · Accepted solution]

Só para ficar mais fácil:

vDataInicio possuirá o valor 01/01/2017

vDataFim possuirá o valor de hoje, 19/01/2018

Agora precisa saber se o intervalo a ser buscado realmente é essa DATA_FIM

[brunoducatti]

oi Yuri, no caso vou precisar executar no select todas as datas deste intervalo, uma por uma.

[nicolett_yuri]

Pq? Não poderia ser um intervalo, retornando cada data em um campo de sua tabela?