DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Find the difference between 2 rows (date - same column)

  1. #1
    Join Date
    Aug 2013
    Posts
    4

    Find the difference between 2 rows (date - same column)

    Hi,
    I have a table with two columns ID and mydate date type. I need to calculate the difference (number of days) difference between first row date to second and second to third and so on...)
    how this can be achieved?

    ID mydate DIFF_DAYS
    1 01-JAN-13 0
    2 05-JAN-13 4
    3 14-JAN-13 9
    4 15-JAN-13 1

    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    So you have a table with two rows for every ID?

    Code:
    ID mydate
    1 01-JAN-13
    1 01-JAN-12
    2 05-JAN-13
    2 05-JAN-12
    3 14-JAN-13
    3 14-JAN-12
    4 15-JAN-13
    4 15-JAN-12
    Code:
    SELECT ID, MAX(mydate)-MIN(mydate)
       FROM mytable
     GROUP BY ID
     ORDER BY 1;
    If you want to compare each id to the next id, for example compare 1 to 2 and 2 to 3, etc.
    Then you need to use analytics with the lead/lag function.

  3. #3
    Join Date
    Aug 2013
    Posts
    4
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width