6 Replies Latest reply: Nov 25, 2012 4:29 PM by g2500 RSS

    Query - first date before payment date

    g2500
      Hi All,

      How to get the payment date less than the sch date?

      example:

      1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.

      sch date should be less than the payment date.

      Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.

      2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012

      Below is the scripts and output needed.
      create table cust_sch_tbl
      (
      customer_NUMBER     number,
      c_CODE     char(5),
      sch_DATE date
      );
      
      create table cust_payment_tbl
      (
      customer_NUMBER     number,
      c_CODE     char(5),
      Payment_DATE date
      );
      
      
      
      SET DEFINE OFF;
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('11/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('07/27/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('10/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('09/25/2012 16:36:44', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('08/08/2012 16:57:14', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('06/29/2012 13:58:56', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_PAYMEBT_TBL
         (CUSTOMER_NUMBER, C_CODE, PAYMENT_DATE)
       Values
         (4611, 'c1   ', TO_DATE('10/19/2012 16:37:47', 'MM/DD/YYYY HH24:MI:SS'));
      COMMIT;
      
      
      
      SET DEFINE OFF;
      Insert into CUST_SCH_TBL
         (CUSTOMER_NUMBER, C_CODE, SCH_DATE)
       Values
         (4611, 'c1   ', TO_DATE('07/24/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_SCH_TBL
         (CUSTOMER_NUMBER, C_CODE, SCH_DATE)
       Values
         (4611, 'c1   ', TO_DATE('07/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_SCH_TBL
         (CUSTOMER_NUMBER, C_CODE, SCH_DATE)
       Values
         (4611, 'c1   ', TO_DATE('07/27/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_SCH_TBL
         (CUSTOMER_NUMBER, C_CODE, SCH_DATE)
       Values
         (4611, 'c1   ', TO_DATE('10/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CUST_SCH_TBL
         (CUSTOMER_NUMBER, C_CODE, SCH_DATE)
       Values
         (4611, 'c1   ', TO_DATE('10/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
      COMMIT;
      
      
      Output needed:
      
      cust   cust code payment date      sch date
      4611     c1     7/27/2012      7/24/2012
      4611     c1     8/8/2012 16:57      7/27/2012
      4611     c1     10/19/2012 16:37 10/11/2012
      4611     c1     11/15/2012      10/30/2012
      Thanks
      aj
        • 1. Re: Query - first date before payment date
          Frank Kulash
          Hi,
          g2500 wrote:
          Hi All,

          How to get the payment date less than the sch date?
          Do you mean "payment date *greater* than the sch date"? Everything else you posted seems to indicate that sch_date comes before payment_date.
          example:

          1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.

          sch date should be less than the payment date.

          Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.
          Why isn't sch_date 07/24/2012 on every row? Isn't that always the least (or earliest) sch_date?
          2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012 ...
          create table cust_payment_tbl 
          ...
          Insert into CUST_PAYMEBT_TBL
          Test (and, if necessary, correct) your code before posting it. The table name in these INSERT statements is different from the table name in any CREATE TABLE statement.
          ... Output needed:
          
          cust   cust code payment date      sch date
          4611     c1     7/27/2012      7/24/2012
          4611     c1     8/8/2012 16:57      7/27/2012
          4611     c1     10/19/2012 16:37 10/11/2012
          4611     c1     11/15/2012      10/30/2012
          What does each row of ouptur represent?
          Why do you want only those 4 rows, and not, say 6 rows:
          CUSTOMER_NUMBER C_COD PAYMENT_DA SCH_DATE
          --------------- ----- ---------- ----------
                     4611 c1    7/27/2012  7/26/2012
                     4611 c1    8/8/2012   7/27/2012
                     4611 c1    9/25/2012  7/27/2012
                     4611 c1    10/11/2012 7/27/2012
                     4611 c1    10/19/2012 10/11/2012
                     4611 c1    11/15/2012 10/30/2012
          I'm not sure what role do customer_number and c_code play in this problem.

          Depending on your answers, you may want something like this:
          WITH    got_prev_payment_date     AS
          (
               SELECT     customer_number
               ,     c_code
               ,     payment_date
               ,     LAG (payment_date) OVER ( PARTITION BY  customer_number
                                            ,              c_code
                                     ORDER BY     payment_date
                                   )     AS prev_payment_date
               FROM    cust_payment_tbl
          --     WHERE     ...     -- If you need any filtering, put it here
          )
          SELECT    p.customer_number
          ,       p.c_code
          ,       p.payment_date
          ,       MIN (s.sch_date)     AS sch_date
          FROM       got_prev_payment_date     p
          JOIN       cust_sch_tbl          s  ON   s.customer_number     = p.customer_number
                                   AND     s.c_code          = p.c_code
                                 AND     s.sch_date          < p.payment_date
                                 AND     s.sch_date          >= NVL ( p.prev_payment_date
                                                            , DATE '0001-01-01'     -- Or any impossibly early date
                                                         )
          GROUP BY  p.customer_number
          ,       p.c_code
          ,       p.payment_date
          ORDER BY  p.customer_number
          ,       p.c_code
          ,       p.payment_date
          ;
          • 2. Re: Query - first date before payment date
            g2500
            Frank,

            mistake... sch date less than payment date.


            yes there can be multiple payment dates... does always match the count.

            //The table name in these INSERT statements is different from the table name in any CREATE TABLE statement.

            next time sure will test before posting :-) sorry about that.

            only four rows since we need only the 1st instance before each payment date.

            Thanks so much Frank. you make it look so simple. you rock.

            Have a wonderful day. :-)
            • 3. Re: Query - first date before payment date
              g2500
              Hi Frank,

              How can we add few more columns in the final query?

              when i add these below columns my output is changing. Thanks.
               p.customer_number
              ,       p.c_code
              ,       p.payment_date
              ,       MIN (s.sch_date)     AS sch_date
              ,  s.emp_id
              , p.cust_address
              FROM       got_prev_payment_date     p
              JOIN       cust_sch_tbl     
              • 4. Re: Query - first date before payment date
                g2500
                I added another join to it...
                WITH    got_prev_payment_date    AS
                (
                    SELECT    customer_number
                    ,    c_code
                    ,    payment_date
                    ,    LAG (payment_date) OVER ( PARTITION BY  customer_number
                                             ,            c_code
                                      ORDER BY    payment_date
                                    )    AS prev_payment_date
                    FROM    cust_payment_tbl
                --    WHERE    ...    -- If you need any filtering, put it here
                )
                t as (
                SELECT    p.customer_number
                ,      p.c_code
                ,      p.payment_date
                ,      MIN (s.sch_date)    AS sch_date
                FROM      got_prev_payment_date    p
                JOIN      cust_sch_tbl        s  ON   s.customer_number    = p.customer_number
                                     AND    s.c_code        = p.c_code
                                   AND    s.sch_date          < p.payment_date
                                       AND     s.sch_date          >= NVL ( p.prev_payment_date
                                                                  , DATE '0001-01-01'     -- Or any impossibly early date
                                                               )
                GROUP BY  p.customer_number
                ,       p.c_code
                ,       p.payment_date
                ORDER BY  p.customer_number
                ,       p.c_code
                ,       p.payment_date)
                select t.*,a.emp_id from t, cus_sch_tbl a
                where
                t.customer_number =  a.customer_number;
                • 5. Re: Query - first date before payment date
                  Frank Kulash
                  Hi,
                  g2500 wrote:
                  How can we add few more columns in the final query?
                  That depends on what you're trying to do (which I still don't understand), what your data is like (post new CREATE TABLE and/or INSERT statements if the ones you posted earlier aren't sufficient) and the results you want from that data. Explain how you get those results from that data, and explain what role every column plays in the problem.
                  when i add these below columns my output is changing...
                  You want the output to change, don't you? If you don't want the output to change, then don't change the query.

                  Perhaps you mean the output is not what you want. Without knowing what output you do want, I can't help much.
                  • 6. Re: Query - first date before payment date
                    g2500
                    Hi Frank,

                    For now, join in query is helping me. keep you posted. Thanks Again.

                    ajr