0

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
dohan_rivas
  • 93
  • 5
  • and your question is... – Sergey Aug 31 '22 at 06:33
  • In WHERE clause use BETWEEN operator between dates – Nickname_used Aug 31 '22 at 06:36
  • 3
    What DBMS you are using? It would be more clear if you post a sample of input and expected output. – ahmed Aug 31 '22 at 06:37
  • 4
    you can create the calendar table and perform a join to it – Sergey Aug 31 '22 at 06:38
  • @Sergey, I should have mentioned that I only have read access to this database, so no creating temp tables. I'll add it to my question now. – dohan_rivas Aug 31 '22 at 06:42
  • 1
    The solution you are looking for is easy possible to do in code. Is there a reason why this needs to be done in SQL? – Webdeveloper_Jelle Aug 31 '22 at 06:42
  • Please specify database you use – Sergey Aug 31 '22 at 06:45
  • Do you want every day in the date range or every day for which at least one meter entry exists? – Thorsten Kettner Aug 31 '22 at 06:48
  • @Sergey&@Ahmed - I'm using an Oracle DB, but only have access to the WebSQLEditor. @Jbadminton - I would if it was possible, no way to connect to the DB with python for example. All they gave us access to is a webSQLEditor. – dohan_rivas Aug 31 '22 at 06:50
  • On a side note, never do this: `AND Date > '1-AUG-2022'`. `'1-AUG-2022'` is a string. If `Date`is also a string (which should not be the case), you'll get completely unexpected results. If `Date` is a date (as it should be) or a timestamp, then the query may work or fail with a runtime error, depending on session settings. Use date literals instead: `AND "Date" > DATE '2022-08-01'`. – Thorsten Kettner Aug 31 '22 at 06:52
  • 2
    The typical SQL solutions: For e"very day in the date range" use a recursive query to generate the dates, then outer join your data. For "every day for which at least one meter entry exists": Cross join distinct dates with distinct meters, then outer join your data. – Thorsten Kettner Aug 31 '22 at 06:57
  • @Thorsten - Thanks for the advice. I need every day in the date range for the meter that I specify in the query. But if the day is missing, it'll just have a gap, which is not what I want. – dohan_rivas Aug 31 '22 at 06:58
  • Okay, then use a recursive query. – Thorsten Kettner Aug 31 '22 at 06:59

2 Answers2

2

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

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

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;
Dalmas Otieno
  • 109
  • 5