CODEWITHSUNDEEP
sqloracle-databasejoin
antanas_sepikas
antanas_sepikas

Reputation: 5714

Oracle sql inner join on dates range

I am trying to write join that joins on ids and date range:

SELECT * FROM C_INVOICE CI 
INNER JOIN C_CONVERSION_RATE CCR 
    ON CCR.C_CURRENCY_ID=CI.C_CURRENCY_ID 
    AND CI.DATEINVOICED BETWEEN CCR.VALIDFROM AND CCR.VALIDTO

But the result I am getting is join from only from

ON CCR.C_CURRENCY_ID=CI.C_CURRENCY_ID

IN theory this should work just fine, and I don't get any errors, but result is not what I want

EDIT

--Full query
SELECT * FROM C_INVOICE CI 
INNER JOIN C_CONVERSION_RATE CCR 
    ON CCR.C_CURRENCY_ID=CI.C_CURRENCY_ID 
    AND CI.DATEINVOICED BETWEEN CCR.VALIDFROM AND CCR.VALIDTO
WHERE CI.C_INVOICE_ID='1019748';

CI.DATEINVOICED = 15-FEB-16 CI.C_CURRENCY_ID = 100

There are many rows inside C_CONVERSION_RATE table with C_CURRENCY_ID = 100 but only one row falls into interval. 

CI.DATEINVOICED(15-FEB-16) BETWEEN CCR.VALIDFROM(15-FEB-16) AND CCR.VALIDTO(15-FEB-16)

In this specific case CCR.VALIDFROM, CCR.VALIDTO are both equal to same date 15-FEB-16 but in other cases it can be few days interval

Anyway, the result I am expecting is single row where currency id and date interval matches. However, I am getting all the rows where currency id matches.

EDIT

Modified query a bit

SELECT 
ci.dateinvoiced,
ccr.validfrom,
ccr.validto
FROM C_INVOICE CI 
INNER JOIN C_CONVERSION_RATE CCR 
    ON CCR.C_CURRENCY_ID=CI.C_CURRENCY_ID 
    AND CI.DATEINVOICED BETWEEN CCR.VALIDFROM AND CCR.VALIDTO
WHERE CI.C_INVOICE_ID='1019748';

the result from this query I am getting

    15-FEB-16   07-MAR-11   29-JAN-56
    15-FEB-16   02-MAR-11   29-JAN-56
    15-FEB-16   27-MAR-11   29-JAN-56
    15-FEB-16   07-FEB-11   29-JAN-56
    15-FEB-16   18-JAN-12   29-JAN-56

ccr.validto seem to be way off 29-JAN-56 there no such date in that column don't know what causing this problem

Upvotes: 2

Views: 4841

Answers (1)

Sentinel
Sentinel

Reputation: 6449

It looks like your C_CONVERSION_RATE table contains many rows with a VALIDTO date representative of an arbitrary end-of-time value of 29-JAN-59 (century unknown). If you are looking for the most recently valid conversion rate relative to the DATEINVOICED, you may need to use an analytic function to determine the next valid VALIDFROM value:

WITH CCR AS (
  SELECT CCR.*
       , LEAD(CCR.VALIDFROM,1,CCR.VALIDTO) 
         OVER (PARTITION BY CCR.C_CURRENCY_ID 
                   ORDER BY CCR.VALIDFROM
                          , CCR.VALIDTO
         ) VALID_TO  -- note the underscore ;)
    FROM C_CONVERSION_RATE CCR
)
SELECT *
  FROM C_INVOICE CI 
 INNER JOIN CCR 
    ON CCR.C_CURRENCY_ID=CI.C_CURRENCY_ID 
   AND CI.DATEINVOICED BETWEEN CCR.VALIDFROM AND CCR.VALID_TO -- note the underscore
   AND CI.DATEINVOICED <> CCR.VALID_TO -- Make it a half open interval by not
                                       -- including the exact end date since
                                       -- it is actually the next start date.

Upvotes: 1

Related Questions