Value for missing data in SQL-CodePudding

Question for the SQL gurus. I have a table with 3 columns. [Date, Meter, Quality], where there will only be one line per date for each meter. As an example:

SELECT * FROM MyDB WHERE Meter = 'MeterX' AND Date > '1-AUG-2022' AND Date <= '5-AUG-2022' ORDER BY Date;

I would query much larger date ranges so would usually miss if there is a date missing. Is there a way that I can have a value returned in the Quality column like "Missing" if that partiqular day is missing from the database? This means that I also need the missing date in the Date column. I also only have read access, so no creating temp tables to join with.

Thank you.

CodePudding user response：

Use a PARTITIONed OUTER JOIN to a row-generator:

SELECT c.day,
       m.meter,
       COALESCE(m.quality, 0) AS quality
FROM   (
         SELECT DATE '2022-08-01'   (LEVEL - 1) AS day
         FROM   DUAL
         CONNECT BY DATE '2022-08-01'   (LEVEL - 1) <= DATE '2022-08-05'
       ) c
       LEFT OUTER JOIN MyDB m
       PARTITION BY (m.meter)
       ON (c.day <= m."DATE" and m."DATE" < c.day   1)
WHERE  m.Meter = 'MeterX'
ORDER BY c.day;

Which, for the sample data:

CREATE TABLE mydb ("DATE", meter, quality) AS
SELECT DATE '2022-08-01', 'MeterX', 42 FROM DUAL UNION ALL
SELECT DATE '2022-08-02', 'MeterX', 23 FROM DUAL UNION ALL
SELECT DATE '2022-08-04', 'MeterX',  7 FROM DUAL UNION ALL
SELECT DATE '2022-08-05', 'MeterX', 99 FROM DUAL;

Outputs:

DAY METER QUALITY

01-AUG-22 MeterX 42

02-AUG-22 MeterX 23

03-AUG-22 MeterX 0

04-AUG-22 MeterX 7

05-AUG-22 MeterX 99

DAY	METER	QUALITY
01-AUG-22	MeterX	42
02-AUG-22	MeterX	23
03-AUG-22	MeterX	0
04-AUG-22	MeterX	7
05-AUG-22	MeterX	99

db<>fiddle here

CodePudding user response：

for postgres this could work for you

with date_dimension as (
    SELECT dd::DATE
    FROM generate_series
            ( '2022-08-01'::timestamp 
            , '2022-08-05'::timestamp
            , '1 day'::interval) dd
)
select * 
from my_table
left join date_dimension on date_dimension.dd = my_table.Date
where Meter = 'MeterX' 
and Date > '2022-08-01' 
and Date <= '2022-08-05' 
order by Date;