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!

Inner join on variable date range-

Discussion in 'SQL PL/SQL' started by Mattz, Aug 31, 2015.

  1. Mattz

    Mattz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Houston
    SQL Guru's

    Scenario-
    There are four producing wells contributing to the total production on a platform . Wells A,B,C have metered data on them, which is monitored intermittently (not metered everyday),
    This Metered data as shown below in the excel (TABLE B) to decide each wells contrubution to the total production ,except WELL D . So Modus operandi is to find the production numbers
    for A , B & C based on the metered numbers, then deduce this off the total production value to get the production amount for D
    Challenges - Metered data is not available everyday so when we try to join on date it gets tricky. This is where I Need help
    I may not have the metered data everyday - So say for Eg, on 30th for WELL A I have metered data, and 31st I don't,, then production values for 31st will get the default metered value from 30th .


    Attached is the Excel version on what I have Vs What I need to get. Hope this helps you to better understand the situation, Any help our guidance is very much appreciated
     

    Attached Files:

  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I seriously doubt that in a similar situation I'd try for a pure-SQL solution. Getting the output for a given day for Wells A-C would be easier with a function like the below. You can then perform a query from the production table alone (i.e. no need for joins) calling the function for each of the Wells A-C for the given value in PROD_DATE.

    Code (Text):

    FUNCTION metered_output(p_date    DATE,
                            p_well    VARCHAR2)
    RETURN NUMBER
    AS
      v_retval    NUMBER;
      v_alt_date  DATE;
    BEGIN
      SELECT MAX(output)
      INTO   v_retval
      FROM   well_test_data
      WHERE  prod_date = p_date
      AND    well_name = p_well;

      IF v_retval IS NULL THEN
        SELECT MAX(prod_date)
        INTO   v_alt_date
        FROM   well_test_data
        WHERE  prod_date < p_date
        AND    well_name = p_well;

        SELECT MAX(output)
        INTO   v_retval
        FROM   [daily_output_table]
        WHERE  prod_date = v_alt_date
        AND    well_name = p_well;
      END IF;

      RETURN NVL(v_retval, 0);
    END metered_output;
     
    Mattz likes this.
  3. Mattz

    Mattz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Houston
     
  4. Mattz

    Mattz Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Houston
    thank you very much