find difference between dates from a column in oracle

I have this data
Emp#           eff_dt                type       seq_nbr     Expected column
1234            7/18/2011      HIRE      1                null
1234           11/23/2012      TERM      2                 16
1234           9/8/2014              HIRE      3                 21
1234            8/23/2015      TERM      4                 11

i need to add another column which would give me difference between eff_dt in Months.
need_solutionAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Abhimanyu SuriDatabase EngineerCommented:
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.
0
awking00Information Technology SpecialistCommented:
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#;
0
Abhimanyu SuriDatabase EngineerCommented:
Thanks for correctio "awking" it appears '-' got copied over for  ','
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

need_solutionAuthor Commented:
Thanks much for the solution but the ORacle version we have in our organization does not support LAG() function
0
Abhimanyu SuriDatabase EngineerCommented:
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
0
SharathData EngineerCommented:
try this.
SELECT t1.*,CAST(MONTHS_BETWEEN(t1.eff_dt, t2.eff_dt) AS INTEGER) Expected_Column
  FROM your_table t1
  LEFT JOIN your_table t2 ON t1.seq_nbr = t2.seq_nbr + 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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;
0
need_solutionAuthor Commented:
Thank you Sharath, it worked!
0
awking00Information Technology SpecialistCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.