Querying data as showed below there is no record on database for day 2021-10-03.

I would like to execute the query using date between as SELECT ... WHERE date BETWEEN '2021-10-01' AND '2021-10-05' and in case of do not exist data for a specific day, to retrieve zero as exemplified below:

Is it possible? in bigQuery?

I tried the query below, but retrieved duplicated values.

WITH `project.myproject` AS (
 SELECT
  DATA_VENDA AS date,
  CAST(SUM(VLR_VENDA_TABELA) AS FLOAT64) AS total,
  FROM `project.myproject`
WHERE
  (DATA_VENDA BETWEEN '2020-10-02'
    AND '2020-10-07')
  AND COD_CP = '0000010232'
GROUP BY
  DATA_VENDA
ORDER BY
  DATA_VENDA
),
dates AS (
  SELECT total, date
  FROM `project.myproject`, UNNEST(GENERATE_DATE_ARRAY(date('2020-10-02'), date('2020-10-07'))) AS date
)
SELECT d.date, IFNULL(t.total, 0) total
FROM dates d
LEFT JOIN `project.myproject` t
ON d.date = t.date
AND d.total = t.total 
ORDER BY d.date

CodePudding user response：

I found out the answers running command below. The difference from that to this is that in this new one I removed the line AND d.total = t.total, who was responsible for creating duplicated data. The final answer follow below:

WITH `project.myproject` AS (
 SELECT
  DATA_VENDA AS date,
  CAST(SUM(VLR_VENDA_TABELA) AS FLOAT64) AS total,
  FROM `project.myproject`
WHERE
  (DATA_VENDA BETWEEN '2020-10-02'
    AND '2020-10-07')
  AND COD_CP = '0000010232'
GROUP BY
  DATA_VENDA
ORDER BY
  DATA_VENDA
),
dates AS (
  SELECT total, date
  FROM `project.myproject`, UNNEST(GENERATE_DATE_ARRAY(date('2020-10-02'), date('2020-10-07'))) AS date
)
SELECT d.date, IFNULL(t.total, 0) total
FROM dates d
LEFT JOIN `project.myproject` t
ON d.date = t.date
ORDER BY d.date

CodePudding user response：

You can simply do that with the common table expression(CTE) as shown below.

DECLARE @Datatemp TABLE (
 Id INT IDENTITY(1,1) NOT NULL,
 CDate DATETIME,
 Val INT
)

INSERT INTO @Datatemp SELECT '2021-10-01',10
INSERT INTO @Datatemp SELECT '2021-10-02',50
INSERT INTO @Datatemp SELECT '2021-10-04',24
INSERT INTO @Datatemp SELECT '2021-10-05',18


;WITH DateTemp(Date) AS (
  SELECT CAST('2021-10-01' AS DATETIME)
  UNION ALL
  SELECT [Date] 1
  FROM DateTemp
  WHERE [Date] < '2021-10-05'
 )
SELECT     DateTemp.[Date] CDat
          ,ISNULL(t.Val, 0) Val
FROM      DateTemp
LEFT JOIN @Datatemp t ON t.CDate = DateTemp.[Date]
ORDER BY  DateTemp.[Date]
--OPTION   (MAXRECURSION 0) 

By default number of iterations for recursive CTE is 100. As long as this number is exceeded, the query will be interrupted and an error will be generated. If you want to remove this restriction, you can specify MAXRECURSION 0.