Solved

# find difference between dates from a column in oracle

Posted on 2016-08-08
69 Views
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.
0
Question by:need_solution
• 3
• 3
• 2
• +1

LVL 4

Expert Comment

ID: 41747962

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

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#;
0

LVL 4

Expert Comment

ID: 41748768
Thanks for correctio "awking" it appears '-' got copied over for  ','
0

Author Comment

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

LVL 4

Expert Comment

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,
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

LVL 40

Accepted Solution

Sharath earned 500 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;
``````
0

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))
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

Author Closing Comment

ID: 41749374
Thank you Sharath, it worked!
0

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.
0

## Featured Post

Question has a verified solution.

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