CODEWITHSUNDEEP
sqloracle-databasedatetimeplsqldate-arithmetic
elszeus
elszeus

Reputation: 131

Compare dates from two queries PL/SQL

I have two queries , the first gets some data back from my table, the second query displays all half hour times between two specified dates. Is there a way of comparing the date results from query 1 from query 2 and merge the two results together when the date from query 2 doesn't exist in query 1 result.

I'll attach a little diagram to show what I mean. enter image description here

Query 1:

SELECT 
reading_date, 
reading_value    
FROM DCM_READING
WHERE reading_date BETWEEN TO_DATE('17-NOV-2019' || ' 000000', 'DD-MON-YYYY HH24MISS')
AND  TO_DATE('19-NOV-2019'   || ' 235959', 'DD-MON-YYYY HH24MISS')
ORDER BY reading_date;

Query 2:

  select TO_DATE('17-NOV-2019' || ' 000000', 'DD-MON-YYYY HH24MISS') + 
         ( level / 48 ) dt
from   dual
connect by level <= ( 48 + ( 48 * 
    (  TO_DATE('19-NOV-2019' || ' 000000', 'DD-MON-YYYY HH24MISS') - 
         TO_DATE('17-NOV-2019' || ' 000000', 'DD-MON-YYYY HH24MISS') )
  ) 
) ;

Upvotes: 0

Views: 540

Answers (1)

GMB
GMB

Reputation: 222582

You can enumerate the timestamps you want in a CTE, then bring the table with a left join:

with cte (reading_date) as (
    select date '2020-11-17' from dual
    union all
    select reading_date + interval '30' minute 
    from cte 
    where reading_date + interval '30' minute < date '2020-11-19'
)
select c.reading_date, d.reading_value
from cte c
left join dcm_reading d on d.reading_date = c.reading_date
order by c.reading_date

I like to use recursive queries rather than Oracle specific connect by syntax, because they are standard SQL - but that's mostly a matter of taste, the logic remains the same.

Upvotes: 2

Related Questions