1 Reply Latest reply: Jan 30, 2013 1:32 PM by Frank Kulash RSS

    Re Concilation Logic Help

    965860
      Hi,

      I have a requirement to join 2 tables and add re-concilation on dates between these 2 tables.
      For example,
      Lets say the driving table is CUSTOMER and the detail table is PRODUCT.

      Data in both tables,
      CUSTOMER
      cust_id     start_dt     end_dt
      1000     1/1/1980     1/1/2008
      1000     1/1/2008     12/31/2990
      2000     1/1/2007     12/31/2999

      PRODUCT
      cust_id     prdct_id     start_dt     end_dt
      1000     p1     1/1/1994     1/1/2007
      1000     p2     1/1/2007     1/1/2009
      1000     p1     1/1/2009     12/31/2999
      2000     pp1     1/1/1990     1/1/2005
      2000     pp2     1/1/2005     1/1/2008
      2000     pp3     1/1/2008     12/31/2999

      OUTPUT
      cust_id     prdct_id     start_dt     end_dt
      1000     NULL     1/1/1980     1/1/1994
      1000     p1     1/1/1994     1/1/2007
      1000     p2     1/1/2007     1/1/2009
      1000     p1     1/1/2009     12/31/2990
      2000     pp2     1/1/2007     1/1/2008
      2000     pp2     1/1/2008     12/31/2999


      Can someone help me write a sql query to achieve the above output using 2 tables?

      Thanks in advance.
        • 1. Re: Re Concilation Logic Help
          Frank Kulash
          Hi,

          That looks like a job for the analytic LAG function:
          SELECT       p.cust_id
          ,       p.prdct_id
          ,       LAG ( p.start_dt
                     , 1
                     , c.start_dt
                     ) OVER ( PARTITION BY  p.cust_id
                                 ORDER BY          p.start_dt
                         )          AS start_dt
          ,       p.start_dt           AS end_dt
          FROM       customer  c
          JOIN       product   p  ON  p.cust_id     = c.cust_id
          ORDER BY  p.cust_id
          ,            p.start_dt
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}