This discussion is archived
7 Replies Latest reply: Nov 20, 2012 9:47 AM by user13179060 RSS

Query - closest date after

g2500 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    xtender Pro
    Currently Being Moderated
    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
    ManguilibeKAO Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    select a.* from cus_loan_track a
    where a.emi_sch_date<a.emi_payment_date;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points