Select dates that are not in the table

Question

Select dates that are not in the table

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I need to set up a report for a customer with the billing for each day of a given month.
So the 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 my query fetches the month and year and makes a date.
I know it has easier ways but the version of firebird is old because it already has another system using it.

At last this is my 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

This query returns the following

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

Ex:

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

sql query firebird

asked by anonymous 14.08.2018 / 13:32

2 answers

1

You can try something like this (code not tested!):

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

The syntax or use of the functions may not be 100% correct, you may have to make some "adjustments".

14.08.2018 / 13:59

I am trying to do a rollback of my querys only that gives me an error in ExecuteScalar What is Mobile and Ubiquitous Computing?

score 3 · Accepted Answer

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 have not connected the CTE table ("WITH RECURSIVE DATAHORA AS") correctly with your select then test and adapt correctly for your case.

Why recursive?

In summary, RECURSIVE is used to indicate that the query will repeat itself, that is, it is a query that is repeated again and again, each time using the results of the previous iteration. This can be very 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