CODEWITHSUNDEEP
sqloracle11g
scof93
scof93

Reputation: 389

Pass min and max date from table to select query

I needed to fill gaps between dates so i got this sql query:

select * from (
  SELECT null as ID, to_date('15/08/28', 'yy/mm/dd') + ROWNUM - 1 as DATE_OF_RATE, null as VALUE, null as CURRENCY_ID
  FROM dual
  CONNECT BY LEVEL <= to_date('15/09/05', 'yy/mm/dd') - to_date('15/08/28', 'yy/mm/dd') + 1
) empCur
left join CURRENCY_RATE cr on TRUNC(empCur.DATE_OF_RATE) = (cr.DATE_OF_RATE)
AND cr.currency_id = 4;

enter image description here Instead of hard coded dates above i need to pass min and max date_of_rate from currency_rate table. So i tried this, but i got diffrent result:

select * from (
  SELECT null as ID, (select min(date_of_rate) from currency_rate) + ROWNUM - 1 as DATE_OF_RATE, null as VALUE, null as CURRENCY_ID
  FROM dual
  CONNECT BY LEVEL <= (select max(date_of_rate) - min(date_of_rate) + 1 from currency_rate)
) empCur
left join CURRENCY_RATE cr on TRUNC(empCur.DATE_OF_RATE) = (cr.DATE_OF_RATE)
where cr.date_of_rate between to_date('15/08/28', 'yy/mm/dd') and to_date('15/09/05', 'yy/mm/dd')
AND cr.currency_id = 4;

enter image description here

As you can see this didn't fill the gaps between date ranges. Is it even possible what i'm trying to achieve?

Upvotes: 0

Views: 1442

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your query is basically fine. The problem is the where clause. That condition should be on the first table:

select *
from (select  null as ID, (select min(date_of_rate) from currency_rate) + ROWNUM - 1 as DATE_OF_RATE, null as VALUE, null as CURRENCY_ID
      from dual
      connect by level <= (select max(date_of_rate) - min(date_of_rate) + 1 from currency_rate)
     ) empCur left join
     CURRENCY_RATE cr
     on empCur.DATE_OF_RATE = cr.DATE_OF_RATE and
        cr.currency_id = 4
where empCur.DATE_OF_RATE >= date '2015-08-28' and
      empCur.DATE_OF_RATE <= date '2015-09-05' ;

I don't see why TRUNC() is necessary. If it is, just add it back in.

Upvotes: 1

Kevin D
Kevin D

Reputation: 98

I find using temp variables can help. Here's what I would do. Please note my syntax is probably shakey as I'm doing this from memory. Some light googling will get you the rest of the way though.

Step 1. Create some temp variables. These need to get run in one command.

DECLARE @MinDate DATETIME;
DECLARE @MaxDate DATETIME;

@MinDate = SELECT Min([DATE_OF_RATE]) FROM CURRENCY_RATE; 
@MaxDate = SELECT Max([DATE_OF_RATE]) FROM CURRENCY_RATE;

Step 2. Create a calendar table of all possible dates, with literally date as the only column. Do this manually if you need to. You can always create a calendar table with days up to 2050, well after this system will no longer be in use.

Step 3. Do the left join from the calendar table to the rates table on the date. This will give you a row regardless of gaps in the rates.

SELECT c.CalDate FROM Calendar c
LEFT JOIN CURRENCY_RATE cr
ON c.CalDate = cr.DATE_OF_RATE

Step 4. Filter the new table where the date is in the min/max range using temp table

SELECT c.CalDate FROM Calendar c
LEFT JOIN CURRENCY_RATE cr
ON c.CalDate = cr.DATE_OF_RATE
WHERE c.CalDate >= @MinDate AND c.CalDate <= @MaxDate

I hope this helps.

Upvotes: 0

Related Questions