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!

Dates in same column

Discussion in 'SQL PL/SQL' started by ertweety, Oct 19, 2016.

  1. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    My data looks as follows;

    Acct_id Score_dt Score
    1259 04/01/2016 100
    1259 04/21/2016 125
    1259 07/05/2016 110

    I would like the score_dt column to be translated into 2 columns. Below is what I am hoping for.

    Acct_id Score_dt Score Score_start_dt Score_end_dt
    1259 04/01/2016 100 04/01/2016 04/20/2016
    1259 04/21/2016 125 04/21/2016 07/04/2016
    1259 07/05/2016 110 07/05/2016
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to use the LEAD() function to produce that output:

    Code (SQL):
    SQL> CREATE TABLE scores(
      2  Acct_id    NUMBER,
      3  Score_dt   DATE,
      4  Score              NUMBER);

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO scores
      3  VALUES (1259, to_date('04/01/2016','MM/DD/RRRR'), 100)
      4  INTO scores
      5  VALUES (1259, to_date('04/21/2016','MM/DD/RRRR'), 125)
      6  INTO scores
      7  VALUES (1259, to_date('07/05/2016','MM/DD/RRRR'), 110)
      8  SELECT * FROM dual;

    3 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT acct_id, score_dt, score, score_dt score_start_dt, lead(score_dt - 1) OVER (partition BY acct_id ORDER BY score_dt) score_dt_end
      2  FROM scores;

       ACCT_ID SCORE_DT       SCORE SCORE_STA SCORE_DT_
    ---------- --------- ---------- --------- ---------
          1259 01-APR-16        100 01-APR-16 20-APR-16
          1259 21-APR-16        125 21-APR-16 04-JUL-16
          1259 05-JUL-16        110 05-JUL-16

    SQL>
     
    ertweety likes this.
  3. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Both answers work. Thanks so much!