find difference between dates from a column in oracle

Please try this

  SELECT emp#,
         eff_date,
         LAG (eff_dt) OVER (PARTITION BY emp# ORDER BY eff_dt),
         MONTHS_BETWEEN (
            eff_date - LAG (eff_dt) OVER (PARTITION BY emp# ORDER BY eff_dt))
    FROM TABLE_TEST
ORDER BY emp#

example, for below mentioned data :

42927      8/4/2016 2:00:10.724 AM
42934      8/4/2016 9:00:33.372 AM
43030      8/8/2016 9:00:44.225 AM
42852      7/31/2016 11:01:00.293 PM

Query Result

42852      7/31/2016 11:01:00.293 PM            
42927      8/4/2016 2:00:10.724 AM      7/31/2016 11:01:00.293 PM      0.1
42934      8/4/2016 9:00:33.372 AM      8/4/2016 2:00:10.724 AM      0
43030      8/8/2016 9:00:44.225 AM      8/4/2016 9:00:33.372 AM      0.13

NOTE : Don't take it as an as is solution, you may need to handle NULL and tweak it as per other columns in mentioned example.

A couple of modifications to Abhimanyu Suri's solution - MONTHS_BETWEEN requires two parameters and FLOOR rounds down the months to a whole number -
SELECT emp#, eff_dt, type, seq_nbr,
FLOOR(MONTHS_BETWEEN (eff_dt, LAG (eff_dt) OVER (PARTITION BY emp# ORDER BY eff_dt))) expected_column
FROM your_table
ORDER BY emp#;

Thanks for correctio "awking" it appears '-' got copied over for  ','

Thanks much for the solution but the ORacle version we have in our organization does not support LAG() function

I have created a script based on dba_hist_snapshot, you will have to introduce partition by clause in dense rank for employee_id and also in where clause a join on employee_id

Hopefully dense_rank will work for you :)

SELECT b.snap_id,
       b.begin_interval_time,
        a.begin_interval_time,
       MONTHS_BETWEEN (b.begin_interval_time, a.begin_interval_time)
  FROM (SELECT snap_id,
               begin_interval_time,
               DENSE_RANK () OVER (ORDER BY begin_interval_time) rnk
          FROM dba_hist_snapshot
         WHERE snap_id IN (43012,
                           43020,
                           43028,
                           43032,
                           43037)) b,
       (SELECT snap_id,
               begin_interval_time,
               DENSE_RANK () OVER (ORDER BY begin_interval_time) rnk
          FROM dba_hist_snapshot
         WHERE snap_id IN (43012,
                           43020,
                           43028,
                           43032,
                           43037)) a
 WHERE  b.rnk - 1 = a.rnk (+)
 order by 1

Sample data by lag Query
snap_id     begin_interval_time         LAG (begin_interval_time)     DIFF                      
43012       8/7/2016 3:00:46.993 PM            
43020       8/7/2016 11:00:12.383 PM    8/7/2016 3:00:46.993 PM         0    
43028       8/8/2016 7:00:37.560 AM     8/7/2016 11:00:12.383 PM        0.01
43032       8/8/2016 11:00:50.881 AM    8/8/2016 7:00:37.560 AM         0    
43037       8/8/2016 4:00:06.712 PM     8/8/2016 11:00:50.881 AM        0  

Sample data by above mentioned SQL

snap_id  begin_interval_time            LAG (begin_interval_time)     DIFF                              
43012    8/7/2016 3:00:46.993 PM        
43020    8/7/2016 11:00:12.383 PM       07-AUG-16 15.00.46.993          0
43028    8/8/2016 7:00:37.560 AM        07-AUG-16 23.00.12.383          0.010
43032    8/8/2016 11:00:50.881 AM       08-AUG-16 07.00.37.560          0
43037    8/8/2016 4:00:06.712 PM        08-AUG-16 11.00.50.881          0

What is your version of Oracle? Whatever version, you can probably get by with row_number() and a self-join-
select h1.emp#, h1.eff_dt, h1.type, h1.seq_nbr,
floor(months_between(h1.eff_dt, h2.eff_dt))
from
(select emp#, eff_dt, type, seq_nbr,
 row_number() over (partition by emp# order by eff_dt) rn
 from yourtable) h1
left join
(select emp#, eff_dt,
 row_number() over (partition by emp# order by eff_dt) rn
 from yourtable) h2
on h1.emp# = h2.emp#
and h1.rn - 1 = h2.rn
order by emp#, eff_dt;

Thank you Sharath, it worked!

That works fine for one emp# but what happens to the seq_nbr when a new emp# is encountered? If it re-increments (similar to row_number) for each emp#, then it should also work.