PC Toledo

Resolvi

Como tratar null? apesar de usar IFNULL não funcionou tentei também COALESCE

Nada como um SELECT por cima pra resolver o mais interno

Código: Selecionar todos

SELECT X.MES, IFNULL(X.TOTAL,0) FROM (
WITH RECURSIVE Ano AS (
 SELECT 1 AS mes
 UNION
 SELECT mes + 1 AS mes
 FROM Ano
   WHERE
    Ano.mes < 12
) 
SELECT * FROM Ano
LEFT JOIN 
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO 
  FROM cobrancauti APA
  WHERE
  APA.D_PGTO IS NULL AND
  YEAR( APA.D_VENC )  = 2018
  GROUP BY 
  MONTH( APA.D_VENC )
 ) AS B ON ANO.MES = MONTH( B.VENCIMENTO )

ORDER BY MES ) X
ORDER BY X.MES

Usou recursive e CTE ao mesmo tempo, ou isso já tinha antes?

Tem aquela função de trazer zerado, que não lembro qual era, que acho que tem neste post.
Revi o post: COALESCE()

COALESCE( a, b, c, d, e, f, 0 )

Retorna o primeiro válido, que não seja NULL.

Para um ano ok, mas para 3 anos, 2018 a 2020, USEI UM BETWEEN mas o resultado ficou errado

JoséQuintas escreveu:Usou recursive e CTE ao mesmo tempo, ou isso já tinha antes?

Usei o exemplo do Alexandre, tomei uma surra pra entender mas consegui fazer funcionar, agora o problema é para um período maior por ano

Olá!

asimoes escreveu:Executando essa seleção dá erro

Sim. A seleção está incompleta. A definição de um CTE termina com um ou mais comandos DML (Data Management Language) ou seja, SELECT, INSERT, UPDATE ou DELETE.

Como você precisa selecionar mais de um ano e dentro de cada ano o mês se repete, no JOIN é preciso considerar o ano também...

Modifiquei o CTE para gerar os 36 meses dos anos que vi no teu código (2018,2019 e 2020). Juntei a este novo CTE o código que você postou. Como não tenho as tuas tabelas, não consegui testar. Mas acho que falta pouco. Acho que o caminho é este. Teste o código abaixo e faça as correções necessárias.

Código: Selecionar todos

WITH recursive Datas AS (
  -- "Membro Âncora"
  SELECT
    1 AS Mes,
    2018 AS Ano,
    1 as Loops
  UNION ALL
    -- "Membro Recursivo"
  SELECT
    CASE
      WHEN Loops % 12 = 0 THEN 1
      ELSE Mes + 1
    end,
    CASE
      WHEN Loops % 12 = 0 THEN Ano + 1
      ELSE Ano
    end,
    Loops + 1
  FROM Datas  /* Resultado da última iteração */
  WHERE
    Loops < 36
)
SELECT  *
  FROM (
      SELECT
        D_VENC AS VENCIMENTO,
        Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
        Coalesce(Month(D_VENC), Datas.Mes) AS MES,
        Coalesce(YEAR(D_VENC), Datas.Ano) AS ANO
      FROM cobrancauti APA
      WHERE
        APA.D_PGTO IS NULL
        AND YEAR(APA.D_VENC) BETWEEN 2018
        AND 2020
      GROUP BY
        MONTH(APA.D_VENC) )
      AS b
  LEFT JOIN ( SELECT Mes, Ano 
                FROM Datas 
            ORDER BY Ano, Mes ) AS d 
    ON d.Mes = MONTH(B.VENCIMENTO)
   AND d.Ano = Year(B.VENCIMENTO)
ORDER BY
  b.MES

O CTE eu consigo testar e está funcionando.

Recursive Common Table Expression CTE

Alexandre, deu esse erro

Coloquei NULL para funcionar

Coalesce(Month(D_VENC),null) AS MES,
Coalesce(YEAR(D_VENC), null) AS ANO

Mas o resultado sumarizou os 3 anos em um único ano 2018

Acrescentei MONTH(apa.d_venc) ) no Group By

GROUP BY
year(APA.D_VENC), MONTH(apa.d_venc) )

Mas o resultado para os meses 4 e 9 e 10 de janeiro não sairam na lista com 0,00
Teve mês que teve pagamento ABRIL, SETEMBRO E OUTUBO e não sairam na lista com 0,00
Eles tem que aparecer mesmo com 0,00 se não o Gráfico não funciona

APA.D_PGTO IS NULL aqui que é problema, nessa query se no teve pagamento deveria aparacer com 0,00

Esse aqui fez, mas para um ano, se colocar BETWEEN 2018 AND 2020 não funciona

Código: Selecionar todos

SELECT X.ANO, X.MES AS MES, IFNULL(X.TOTAL,0) AS TOTAL FROM (
WITH RECURSIVE Ano AS (
 SELECT 1 AS MES
 UNION 
 SELECT MES + 1 AS MES
 FROM ANO
   WHERE
    Ano.mes < 12
) 
SELECT * FROM ANO
LEFT JOIN 
( SELECT D_VENC AS VENCIMENTO, Sum( apa.vl_guia) AS TOTAL, YEAR(D_VENC) AS ANO 
  FROM cobrancauti APA
  WHERE
  APA.D_PGTO IS NULL AND
  YEAR( APA.D_VENC ) = 2018
  GROUP BY 
  YEAR( APA.D_VENC ), MONTH( APA.D_VENC )
 ) AS B ON ANO.MES = MONTH( B.VENCIMENTO ) 
ORDER BY MES ) X
ORDER BY  X.MES

Olá!
Ali onde você trocou Datas por NULL o certo é d.mes e d.ano.
Fica difícil sem os dados. Não consegue anexar as estruturas das tabelas com alguns dados?

Inverti o LEFT JOIN agora funcionou

Código: Selecionar todos

WITH recursive Datas AS (
  -- "Membro Âncora"
  SELECT
    1 AS Mes,
    2018 AS Ano,
    1 as Loops
  UNION ALL
    -- "Membro Recursivo"
  SELECT
    CASE
      WHEN Loops % 12 = 0 THEN 1
      ELSE Mes + 1
    end,
    CASE
      WHEN Loops % 12 = 0 THEN Ano + 1
      ELSE Ano
    end,
    Loops + 1
  FROM Datas  /* Resultado da última iteração */
  WHERE
    Loops < 36
)
SELECT  *
  FROM 
  Datas D
  LEFT JOIN
  (
      SELECT
        D_VENC AS VENCIMENTO,
        Coalesce(Sum(apa.vl_guia), 0) AS TOTAL,
        Coalesce(Month(D_VENC),null) AS MES,
        Coalesce(YEAR(D_VENC), null) AS ANO
      FROM cobrancauti APA
      WHERE
        APA.D_PGTO IS NULL
        AND YEAR(APA.D_VENC) BETWEEN 2018
        AND 2020 
       GROUP BY
        year(APA.D_VENC), MONTH(apa.d_venc) ) AS RES ON RES.ANO = D.ANO AND RES.MES  = D.MES

Olá!

Show!!! Se trocar aqueles NULL por d.mes e d.ano acho que melhora.

Obrigado Alexandre e Quintas pelas dicas,

Alexandre muito legal esse recurso RECURSIVE, funcionou perfeito

Já removi a GAMBI do código, RS

PC Toledo

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento

Select com agrupamento