7 Replies Latest reply: Nov 20, 2012 11:47 AM by user13179060 RSS

    Query - closest date after

    g2500
      Hi All

      I am looking for find the latest payment date after the sch date.

      for each sch date in cust id,

      I want to find the closest payment date.

      payment date should be greater than the sch date

      For example :

      cust id 111

      sch date Payment date

      11/01/2012 11/05/2012

      11/12/2012 11/15/2012

      10/17/2012 10/20/2012
      output required
      
      
      CUST_ID     CUST_NAME     EMI_SCH_DATE     EMI_PAYMENT_DATE
      
      111     deb               11/1/2012     11/5/2012
      111     deb               11/12/2012     11/15/2012
      111     deb               10/17/2012     10/20/2012
      856     tom               11/7/2012     11/12/2012
      856     tom               10/2/2012     10/8/2012
      475     john              9/4/2012     9/11/2012
      
      create table cus_loan_track
      (
      cust_id number,
      cust_name char(20),
      emi_sch_date date,
      emi_payment_date date
      );
      
      SET DEFINE OFF;
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (111, 'deb                 ', TO_DATE('11/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (111, 'deb                 ', TO_DATE('11/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (111, 'deb                 ', TO_DATE('10/17/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (111, 'deb                 ', TO_DATE('10/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (111, 'deb                 ', TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (856, 'tom                 ', TO_DATE('11/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (856, 'tom                 ', TO_DATE('10/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/18/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (856, 'tom                 ', TO_DATE('10/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (856, 'tom                 ', TO_DATE('11/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_SCH_DATE, EMI_PAYMENT_DATE)
       Values
         (475, 'john                ', TO_DATE('09/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (475, 'john                ', TO_DATE('10/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUS_LOAN_TRACK
         (CUST_ID, CUST_NAME, EMI_PAYMENT_DATE)
       Values
         (475, 'john                ', TO_DATE('09/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      COMMIT;
      Thanks
      ajr
        • 1. Re: Query - closest date after
          Solomon Yakobson
          Not sure what the logic is, but it looks like:
          SQL> select  *
            2    from  cus_loan_track
            3    where emi_sch_date is not null
            4    order by cust_id,
            5             emi_sch_date
            6  /
          
             CUST_ID CUST_NAME            EMI_SCH_DATE        EMI_PAYMENT_DATE
          ---------- -------------------- ------------------- -------------------
                 111 deb                  10/17/2012 00:00:00 10/20/2012 00:00:00
                 111 deb                  11/01/2012 00:00:00 11/05/2012 00:00:00
                 111 deb                  11/12/2012 00:00:00 11/15/2012 00:00:00
                 475 john                 09/04/2012 00:00:00 09/26/2012 00:00:00
                 856 tom                  10/02/2012 00:00:00 10/18/2012 00:00:00
                 856 tom                  11/07/2012 00:00:00 11/12/2012 00:00:00
          
          6 rows selected.
          
          SQL> 
          SY.
          • 2. Re: Query - closest date after
            Frank Kulash
            Hi,
            g2500 wrote:
            Hi All

            I am looking for find the latest payment date after the sch date.
            Do you mean the earliest payment date after the sch date?
            Cust_id 111 has payments on Oct. 9, Oct,. 20, Nov. 5, Nov. 10 and Nov. 15, so the latest payment after each of the sch dates (Oct. 17, Nov. 1 and Nov. 12) is always Nov. 15.

            To find the earliest payment date after emi_sch_date:
            SELECT    e.cust_id
            ,       e.cust_name
            ,       e.emi_sch_date
            ,       MIN (l.emi_payment_date)     AS emi_payment_date
            FROM       cus_loan_track  e
            JOIN       cus_loan_track  l  ON  e.cust_id     = l.cust_id
                                  AND e.emi_sch_date     <= l.emi_payment_date
            GROUP BY  e.cust_id
            ,       e.cust_name
            ,       e.emi_sch_date
            ;
            Output:
            `  CUST_ID CUST_NAME            EMI_SCH_DA EMI_PAYMEN
            ---------- -------------------- ---------- ----------
                   111 deb                  10/17/2012 10/20/2012
                   111 deb                  11/1/2012  11/5/2012
                   111 deb                  11/12/2012 11/15/2012
                   475 john                 9/4/2012   9/11/2012
                   856 tom                  10/2/2012  10/8/2012
                   856 tom                  11/7/2012  11/12/2012
            • 3. Re: Query - closest date after
              Sayan Malakshinov.
              First variant:
              select t.cust_id
                    ,t.cust_name
                    ,t.emi_sch_date
                    ,(select min(tt.emi_payment_date) from cus_loan_track tt where tt.cust_id=t.cust_id and t.emi_sch_date<=Tt.emi_payment_date) closest_payment_date
              from cus_loan_track t
              where t.emi_sch_date is not null
              /
              Sorry, second variant was wrong

              Regards,
              Sayan Malakshinov

              t replaced with tt: xtender on 14.11.2012 23:17

              Edited by: xtender on 14.11.2012 23:19
              • 4. Re: Query - closest date after
                Manguilibe KAO
                Hi,

                Are you sure , with the insert statements you provided, that the last line in your output( 475     john 9/4/2012     9/11/2012)
                matches a row in your table?
                • 5. Re: Query - closest date after
                  g2500
                  Thanks. that's what is needed.

                  I am looking for the earliest payment date after sch date.


                  can i use analytical function with this? I have other set of columns to select.

                  thanks
                  ajr
                  • 6. Re: Query - closest date after
                    Frank Kulash
                    Hi,
                    g2500 wrote:
                    can i use analytical function with this? I have other set of columns to select.
                    Sure. If you want several columns from the same row (the row with the earliest payment_date), then you can use the analytic ROW_NUMBER function (or RANK, depending on how you want to handle ties).

                    As always, if you'd like help, post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.

                    Perhaps you want something like this:
                    WITH     got_r_num  AS
                    (
                         SELECT     e.cust_id, e.cust_name, e.emi_sch_date
                         ,     l.emi_payment_date
                    --     ,     l.column_2, l.column_3, ...     -- whatever columns you want
                         ,     RANK () OVER ( PARTITION BY  e.cust_id, e.cust_name, e.emi_sch_date
                                               ORDER BY          l.emi_payment_date
                                        )          AS r_num
                         FROM    cus_loan_track  e
                         JOIN     cus_loan_track  l  ON  e.cust_id     = l.cust_id
                                                  AND e.emi_sch_date     <= l.emi_payment_date
                    )
                    SELECT     *     -- or list all columns except r_num
                    FROM     got_r_num
                    WHERE     r_num     = 1
                    ;
                    This is called a Top-N Query .

                    Edited by: Frank Kulash on Nov 19, 2012 8:28 PM
                    • 7. Re: Query - closest date after
                      user13179060
                      select a.* from cus_loan_track a
                      where a.emi_sch_date<a.emi_payment_date;