Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


find difference between dates from a column in oracle

Posted on 2016-08-08
Medium Priority
Last Modified: 2016-08-09
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.
Question by:need_solution
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1

Expert Comment

by:Abhimanyu Suri
ID: 41747962
Please try this

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

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.
LVL 32

Expert Comment

ID: 41748761
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#;

Expert Comment

by:Abhimanyu Suri
ID: 41748768
Thanks for correctio "awking" it appears '-' got copied over for  ','
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

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

Expert Comment

by:Abhimanyu Suri
ID: 41749219
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,
       MONTHS_BETWEEN (b.begin_interval_time, a.begin_interval_time)
  FROM (SELECT snap_id,
               DENSE_RANK () OVER (ORDER BY begin_interval_time) rnk
          FROM dba_hist_snapshot
         WHERE snap_id IN (43012,
                           43037)) b,
       (SELECT snap_id,
               DENSE_RANK () OVER (ORDER BY begin_interval_time) rnk
          FROM dba_hist_snapshot
         WHERE snap_id IN (43012,
                           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          0
43028    8/8/2016 7:00:37.560 AM        07-AUG-16          0.010
43032    8/8/2016 11:00:50.881 AM       08-AUG-16          0
43037    8/8/2016 4:00:06.712 PM        08-AUG-16          0
LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 41749356
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

LVL 32

Expert Comment

ID: 41749359
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))
(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;

Author Closing Comment

ID: 41749374
Thank you Sharath, it worked!
LVL 32

Expert Comment

ID: 41749392
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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question