Select dates that are not in the table

Question

I need to compile a report for a client with the billing for each day of a given month.
Until then beauty, the problem is that now he wants even the days that have no sale, are shown in the table with the value showing zero.

For example, this one of mine query day month and year and makes a date.
I know there are easier ways but the version of Firebird is old because it already has another system using the same.

Anyway this is mine query

SELECT      EXTRACT(DAY FROM p.DATAFECHAMENTO) || '/' || EXTRACT(MONTH FROM p.DATAFECHAMENTO) || '/' || EXTRACT(YEAR FROM p.DATAFECHAMENTO) AS data
        ,   SUM(pp.valor) AS total 
FROM        PEDIDOPAGAMENTO AS pp 
INNER JOIN  PEDIDO          AS p ON pp.codpedido    = p.codpedido 
WHERE       EXTRACT(MONTH FROM p.DATAFECHAMENTO)    = @Mes 
        AND EXTRACT(YEAR FROM p.DATAFECHAMENTO)     = @Ano  
GROUP BY    data

That one query returns the following

These are the days that had something sold, however I wanted to show the days that were not sold anything also with the total = 0

Ex:

01/05/2017 0
02/05/2017 0
03/05/2017 0
...

Answer 1

See if this script can help you:

WITH RECURSIVE DATAHORA AS(
SELECT 1 AS id
      ,CAST(@PRIMEIRO_DIA AS DATE) as tempo   /*Aqui tem que ser assim -> '2018-08-01' sempre o primeiro dia do mês que você quer*/
  FROM RDB$DATABASE                                  
UNION ALL                                                  
SELECT DATAHORA.id +1 AS id                                                   
      ,DATEADD(+1 DAY TO DATAHORA.tempo) as tempo
  FROM DATAHORA                                                                 
 WHERE EXTRACT(MONTH FROM DATAHORA.tempo) = EXTRACT(MONTH FROM DATEADD(+1 DAY TO DATAHORA.tempo)))     
SELECT D.TEMPO        AS DATA
       ,SUM(PP.VALOR) AS TOTAL 
  FROM      DATAHORA        D
  LEFT JOIN PEDIDO          P  ON CAST(P.DATAFECHAMENTO AS DATE) = D.TEMPO 
  LEFT JOIN PEDIDOPAGAMENTO PP ON P.CODPEDIDO                    = PP.CODPEDIDO
 WHERE EXTRACT(MONTH FROM D.TEMPO)  = @MES 
   AND EXTRACT(YEAR FROM D.TEMPO)   = @ANO  
 GROUP BY DATA

It may be that I did not link the CTE table ("WITH RECURSIVE DATAHORA AS") correctly with your select so test and adapt correctly to your case.

Why recursive?

In summary, the RECURSIVE to indicate that the query will repeat itself, that is, it is a query that repeats again and again, each time using the results of the previous iteration. This can be quite useful for producing reports based on hierarchical data.

The recursive member is associated with the anchor member by the operator UNION ALL or UNION DISTINCT.

You can understand a little better here (Ta in English)

Answer 2

You can try something like that (untested code!):

DECLARE DataInicial AS DATE
DECLARE DataAtual   AS DATE

CREATE TEMPORARY TABLE TmpDatas (Data AS DATE)

SET DataInicial = STR_TO_DATE('01/' + CAST(@Mes AS CHAR) + '/' + CAST(@Ano AS CHAR), '%d/%m/%Y')
SET DataAtual   = DataInicial

WHILE MONTH(DataAtual) = MONTH(DataInicial) DO
    INSERT INTO TmpDatas VALUES(DataAtual)

    SET DataAtual = DATE_ADD(DataAtual, INTERVAL 1 DAY)
END WHILE

SELECT      DATE_FORMAT(TMP.Data, '%d/%m/%Y')   AS Data
        ,   IFNULL(SUM(pp.valor), 0)            AS Total 
FROM        TmpDatas        AS TMP
LEFT JOIN   PEDIDOPAGAMENTO AS PP   ON  P.DATAFECHAMENTO    = TMP.Data
LEFT JOIN   PEDIDO          AS P    ON  PP.codpedido        = P.codpedido
WHERE       MONTH(P.DATAFECHAMENTO) = @Mes 
        AND YEAR(P.DATAFECHAMENTO)  = @Ano  
GROUP BY    Data

Syntax or function usage may not be 100% correct, you may have to make some "adjustments".