Choose the next closest date after another in Oracle SQL

user_18238756 0
Viewed 47

How do I select the next closest received date from table2 relative to the requested date in table 1? Included the desired result at the end. I am using Oracle SQL.

Table1: 
PO           RequestedDate
14888        01/12/2018
14733        02/12/2018
14555        05/12/2018

Table2:
 PO            ReceivedDate
 14888         01/11/2018
 14888         01/14/2018
 14733         2/11/2018
 14733         2/12/2018
 14555         07/23/2018
 14555         09/23/2018

Expected Result:

PO         RequestedDate       NearestReceivedDate
14888       01/12/2018          01/14/2018
14733       02/12/2018          02/12/2018
14555       05/12/2018          07/23/2018
oracle sql
4 Answers
WITH TABLE_1(PO,REQUESTED_DATE)AS
(
  SELECT 14888, TO_DATE('01/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14733, TO_DATE('02/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14555, TO_DATE('05/12/2018','MM/DD/YYYY') FROM DUAL  
),
TABLE_2(PO,Received_Date) AS
(
  SELECT 14888,    TO_DATE('01/11/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT  14888,     TO_DATE('01/14/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14733,     TO_DATE('02/11/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14733,     TO_DATE('02/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14555,     TO_DATE('07/23/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 14555,     TO_DATE('09/23/2018','MM/DD/YYYY')  FROM DUAL  
)
SELECT T.PO,CAST(T.REQUESTED_DATE AS DATE),X.RR
FROM TABLE_1 T
CROSS APPLY
(
  SELECT MIN(T2.Received_Date)RR
  FROM TABLE_2 T2
  WHERE T.PO=T2.PO 
   AND CAST(T2.Received_Date AS DATE)>=CAST(T.REQUESTED_DATE AS DATE)
)X

https://dbfiddle.uk/?rdbms=oracle_21&fiddle=81cc657918a214d0f118f870eeb67a3d

user_145355170

From Oracle 12, you can use a LATERAL join and FETCH FIRST ROW ONLY:

SELECT *
FROM   table1 t1
       CROSS JOIN LATERAL (
         SELECT t2.receiveddate
         FROM   table2 t2
         WHERE  t1.po = t2.po
         AND    t1.requesteddate <= t2.receiveddate
         ORDER BY
                t2.receiveddate
         FETCH FIRST ROW ONLY
       )

Which, for the sample data:

CREATE TABLE Table1 (PO, RequestedDate) AS
SELECT 14888, DATE '2018-01-12' FROM DUAL UNION ALL
SELECT 14733, DATE '2018-02-12' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-05-12' FROM DUAL;

CREATE TABLE Table2 (PO, ReceivedDate) AS
SELECT 14888, DATE '2018-01-11' FROM DUAL UNION ALL
SELECT 14888, DATE '2018-01-14' FROM DUAL UNION ALL
SELECT 14733, DATE '2018-02-11' FROM DUAL UNION ALL
SELECT 14733, DATE '2018-02-12' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-07-23' FROM DUAL UNION ALL
SELECT 14555, DATE '2018-09-23' FROM DUAL;

Outputs:

PO REQUESTEDDATE RECEIVEDDATE
14888 12-JAN-18 14-JAN-18
14733 12-FEB-18 12-FEB-18
14555 12-MAY-18 23-JUL-18

db<>fiddle here

user_15092640

You can also use below solution for your purpose

select t1.*, t2.ReceivedDate
from Table1 t1, Table2 t2
WHERE t1.PO = t2.PO
AND t1.RequestedDate <= t2.ReceivedDate
AND t2.ReceivedDate = (
   SELECT MIN(t3.ReceivedDate) 
   FROM Table2 t3 
   WHERE t3.PO = t1.PO 
   AND t1.RequestedDate <= t3.ReceivedDate 
  )
;

db<>fiddle here

user_136191160

As a tricky option you may transform an input dataset to a "vertical" event-like structure, order all the dates and use match_recognize to select the row rigth after the receiveddate. It doesn't require nested loops compared to the lateral join/cross apply, which significantly affects the performance in case of large dataset in Table1, and doesn't multiply the data compared to plain join of two tables and min aggregation.

Based on the @MT0's data:

with t as (
  select
    po,
    /*Prepend numeric constant for stable sort order*/
    '0_REQ' as src,
    RequestedDate as dt
  from table1
  
  union all
  
  select
    po,
    '1_RCV' as src,
    ReceivedDate as dt
  from table2
)
select /*+gather_plan_statistics*/ *
from t
match_recognize(
  partition by po
  /*Order by dates, then by event type*/
  order by dt asc, src asc
  measures
    /*Requested date is taken from a request event*/
    req.dt as RequestedDate,
    /*Received date is taken from a receive event*/
    rcv.dt as ReceivedDate
  pattern (req rcv)
  define
    req as src = '0_REQ',
    rcv as src = '1_RCV' 
)

   PO | REQUESTEDDATE | RECEIVEDDATE
----: | :------------ | :-----------
14555 | 12-MAY-18     | 23-JUL-18   
14733 | 12-FEB-18     | 12-FEB-18   
14888 | 12-JAN-18     | 14-JAN-18

db<>fiddle here

And just to compare at randomly upscaled dataset: db<>fiddle

user_27787100
Related