1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Joining table to another table with the closest date

Discussion in 'SQL PL/SQL' started by Mattz, Dec 9, 2015.

  1. Mattz

    Mattz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Houston
    Hi ,

    I am trying to get dates from a second table which is has well test results from adhoc dates, My goal is to get these results to table A (which is the daily production table).

    Table A (Sample of Daily Transaction table)
    WELLID DATE
    201 11-Sep-15
    201 12-Sep-15
    201 13-Sep-15
    201 19-Sep-15

    502 10-Oct-15
    502 10-Nov-15


    Table B (Well Test Table)
    WELLID TESTDATE YIELDFACTOR
    201 10-Aug-15 500
    201 10-Sep-15 300
    201 11-Oct-15 200

    502 10-Aug-15 50
    502 7-Oct-15 170
    502 15-Oct-15 200

    After joining the data should look like below (with all data from the Daily Transactional table and the corresponding nearest Test dates for that particular well

    WELLID DATE TESTDATE YIELDFACTOR
    201 11-Sep-15 10-Sep-15 300
    201 12-Sep-15 10-Sep-15 300
    201 13-Sep-15 10-Sep-15 300
    201 19-OCT-15 11-Oct-15 200

    502 10-Oct-15 7-Oct-15 170
    502 11-Oct-15 15-Oct-15 200

    Attached is an illustrated Excel data set of for better clarity , Any help is very much appreocated
     

    Attached Files:

    Last edited: Dec 9, 2015
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This seems to work:

    Code (SQL):
    SQL> CREATE TABLE A(
      2          wellid  NUMBER,
      3          sample_dt       DATE);

    TABLE created.

    SQL>
    SQL> CREATE TABLE b(
      2          wellid  NUMBER,
      3          test_dt DATE,
      4          yld_factor      NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO a
      3  VALUES(201, to_date('11-Sep-15','DD-Mon-RR'))
      4  INTO a
      5  VALUES(201, to_date('12-Sep-15','DD-Mon-RR'))
      6  INTO a
      7  VALUES(201, to_date('13-Sep-15','DD-Mon-RR'))
      8  INTO a
      9  VALUES(201, to_date('19-Sep-15','DD-Mon-RR'))
    10  INTO a
    11  VALUES(201, to_date('11-Oct-15','DD-Mon-RR'))
    12  INTO a
    13  VALUES(502, to_date('10-Oct-15','DD-Mon-RR'))
    14  INTO a
    15  VALUES(502, to_date('11-Oct-15','DD-Mon-RR'))
    16  INTO a
    17  VALUES(502, to_date('10-Nov-15','DD-Mon-RR'))
    18  SELECT * FROM dual;

    8 ROWS created.

    SQL>
    SQL> INSERT ALL
      2  INTO b
      3  VALUES(201, to_date('10-Aug-15','DD-Mon-RR'), 500)
      4  INTO b
      5  VALUES(201, to_date('10-Sep-15','DD-Mon-RR'), 300)
      6  INTO b
      7  VALUES(201, to_date('11-Oct-15','DD-Mon-RR'), 200)
      8  INTO b
      9  VALUES(502, to_date('10-Aug-15','DD-Mon-RR'), 50)
    10  INTO b
    11  VALUES(502, to_date('07-Oct-15','DD-Mon-RR'), 170)
    12  INTO b
    13  VALUES(502, to_date('15-Oct-15','DD-Mon-RR'), 200)
    14  INTO b
    15  VALUES(201, to_date('19-Oct-15','DD-Mon-RR'), 200)
    16  SELECT * FROM dual;

    7 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL>
    SQL> SELECT x.wellid, x.sample_dt, x.test_dt, x.yld_factor
      2  FROM
      3  (SELECT a.wellid, a.sample_dt, b.test_dt, b.yld_factor
      4  FROM a, b
      5  WHERE b.wellid = a.wellid) x
      6  WHERE abs(x.test_dt - x.sample_dt) < 9;

        WELLID SAMPLE_DT TEST_DT   YLD_FACTOR
    ---------- --------- --------- ----------
           201 11-SEP-15 10-SEP-15        300
           201 12-SEP-15 10-SEP-15        300
           201 13-SEP-15 10-SEP-15        300
           201 11-OCT-15 19-OCT-15        200
           201 11-OCT-15 11-OCT-15        200
           502 10-OCT-15 15-OCT-15        200
           502 10-OCT-15 07-OCT-15        170
           502 11-OCT-15 15-OCT-15        200
           502 11-OCT-15 07-OCT-15        170

    9 ROWS selected.

    SQL>
     
     
    Sadik likes this.