Selecting closest date relative to a a date in another table (Oracle SQL)-CodePudding

How do I select the closest (either before or after) 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         03/12/2018
 14733         12/12/2018
 14555         07/23/2018
 14555         09/23/2018

Expected Result:

PO         RequestedDate       NearestReceivedDate
14888       01/12/2018          01/11/2018
14733       02/12/2018          12/12/2018
14555       05/12/2018          07/23/2018

CodePudding user response：

From Oracle 12, you can use a correlated sub-query and fetch only the closest row:

SELECT t1.*,
       (
         SELECT receiveddate
         FROM   table2 t2
         WHERE  t1.po = t2.po
         ORDER BY ABS(t1.requesteddate - t2.receiveddate)
         FETCH FIRST ROW ONLY
       ) AS receiveddate
FROM   table1 t1;

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-03-12' FROM DUAL UNION ALL
  SELECT 14733, DATE '2018-12-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 2018-01-12 00:00:00 2018-01-11 00:00:00

14733 2018-02-12 00:00:00 2018-12-12 00:00:00

14555 2018-05-12 00:00:00 2018-07-23 00:00:00

PO	REQUESTEDDATE	RECEIVEDDATE
14888	2018-01-12 00:00:00	2018-01-11 00:00:00
14733	2018-02-12 00:00:00	2018-12-12 00:00:00
14555	2018-05-12 00:00:00	2018-07-23 00:00:00

db<>fiddle here

CodePudding user response：

One option is to rank absolute value of date difference (when you subtract two date datatype values, result is number of days) and then fetch the ones that rank as the highest.

Sample data:

SQL> with
  2  tab1 (po, requesteddate) as
  3    (select 14888, date '2018-01-12' from dual union all
  4     select 14733, date '2018-02-12' from dual union all
  5     select 14555, date '2018-05-12' from dual
  6    ),
  7  tab2 (po, receiveddate) as
  8    (select 14888, date '2018-01-11' from dual union all
  9     select 14888, date '2018-03-12' from dual union all
 10     select 14733, date '2018-12-12' from dual union all
 11     select 14555, date '2018-07-23' from dual union all
 12     select 14555, date '2018-09-23' from dual
 13    ),

Query begins here:

 14  temp as
 15    (select a.po, a.requesteddate, b.receiveddate,
 16       rank() over (partition by a.po order by abs(a.requesteddate - b.receiveddate)) rnk
 17     from tab1 a join tab2 b on a.po = b.po
 18    )
 19  select t.po, t.requesteddate, t.receiveddate as nearestreceiveddate
 20  from temp t
 21  where t.rnk = 1;

        PO REQUESTEDD NEARESTREC
---------- ---------- ----------
     14555 05/12/2018 07/23/2018
     14733 02/12/2018 12/12/2018
     14888 01/12/2018 01/11/2018

SQL>

CodePudding user response：

WITH table1 (po, requesteddate)
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
),
table2 (po, receiveddate)
AS
(
SELECT 14888, TO_DATE('01/11/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14888, TO_DATE('03/12/2018','MM/DD/YYYY') FROM DUAL UNION ALL
SELECT 14733, TO_DATE('12/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  
), sorted_results (po, requesteddate, receiveddate, rn)
AS
(
select t1.po, 
       t1.requesteddate,
       t2.receiveddate,
       row_number() over (partition by t1.po order by ABS(t1.requesteddate - t2.receiveddate)) as rn
  from table1 t1 JOIN table2 t2 ON t1.po = t2.po
)
SELECT po, requesteddate, receiveddate as nearestreceiveddate FROM sorted_results WHERE rn = 1;

        PO REQUESTEDDA NEARESTRECE
---------- ----------- -----------
     14555 12-MAY-2018 23-JUL-2018
     14733 12-FEB-2018 12-DEC-2018
     14888 12-JAN-2018 11-JAN-2018