CODEWITHSUNDEEP
sqloracle-databasedatediff
Sayandip Ghatak
Sayandip Ghatak

Reputation: 303

Calculate no of days between two latest dates for each ID column in Oracle

I have a table like this :

ID Submitted Date
0000120-JUL-2018
0000117-MAR-2017
0000120-JUN-2019
0000220-JUL-2018
0000222-AUG-2018
0000223-APR-2019

I want to calculate the no of days between latest two Submitted Date for each ID.

Like for ID = 00001 , the no of days should be 20-JUN-2019 - 20-JUL-2018.

I am using Oracle 11G.

Upvotes: 0

Views: 363

Answers (3)

Alex Poole
Alex Poole

Reputation: 191520

A slight variation; you can find the previous date for each row, and also assign a ranking based on the date order:

select id, submitted_date,  
  lag(submitted_date) over (partition by id order by submitted_date) as previous_date,
  dense_rank() over (partition by id order by submitted_date desc) as rnk
from your_table;
ID    SUBMITTED_DATE PREVIOUS_DATE        RNK
----- -------------- ------------- ----------
00001 2017-03-17                            3
00001 2018-07-20     2017-03-17             2
00001 2019-06-20     2018-07-20             1
00002 2018-07-20                            3
00002 2018-08-22     2018-07-20             2
00002 2019-04-23     2018-08-22             1

And then use that as an inline view to only get the latest date - which is ranked first:

select id, submitted_date, previous_date,
  submitted_date - previous_date as diff
from
(
  select id, submitted_date,  
    lag(submitted_date) over (partition by id order by submitted_date) as previous_date,
    dense_rank() over (partition by id order by submitted_date desc) as rnk
  from your_table
)
where rnk = 1;

ID    SUBMITTED_DATE PREVIOUS_DATE       DIFF
----- -------------- ------------- ----------
00001 2019-06-20     2018-07-20           335
00002 2019-04-23     2018-08-22           244

Upvotes: 0

Popeye
Popeye

Reputation: 35920

Try this:

SELECT ID, 
MAX(SUBMITTED_DATE - PREV_SUBMITTED_DATE) AS DIFF
FROM
(SELECT ID, SUBMITTED_DATE,  
LAG(SUBMITTED_DATE) OVER (PARTITION BY ID ORDER BY SUBMITTED_DATE) AS PREV_SUBMITTED_DATE
FROM
(SELECT ID, SUBMITTED_DATE,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SUBMITTED_DATE DESC) AS RN
FROM TABLE)
WHERE RN <= 2)
GROUP BY ID;

Or

SELECT ID, MAX(DIFF) AS DIFF FROM
(SELECT ID,
NTH_VALUE(SUBMITTED_DATE,1) OVER (PARTITION BY ID ORDER BY SUBMITTED_DATE DESC) -  
NTH_VALUE(SUBMITTED_DATE,2) OVER (PARTITION BY ID ORDER BY SUBMITTED_DATE DESC) AS DIFF
FROM TABLE)
GROUP BY ID;

Cheers!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

One method uses lag() and lead():

select t.*,
       (submitted_date - prev_sd) as day_diff
from (select t.*,
             lag(submitted_date) over (partition by id order by submitted_date) as prev_sd,
             lead(submitted_date) over (partition by id order by submitted_date) as next_sd
      from t
     ) t
where next_sd is null;

This method avoids any aggregation.

Upvotes: 2

Related Questions