CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Puttu
Puttu

Reputation: 45

Calculating the Average time for last 5 instance or last 3 months for each job in Oracle

I have table which has job name, start_date and end_date, what I am looking is need an average time for each job for last 10 instances or for last 3 months. Please suggest.

Job Start_Date  End_Date
A   1/2/2017 4:47   1/2/2017 4:48
B   5/8/2017 23:09  5/8/2017 23:10
C   2/28/2017 4:56  2/28/2017 4:57
D   2/27/2017 4:05  2/27/2017 4:05
A   2/18/2017 4:38  2/18/2017 4:38
B   2/15/2017 4:23  2/15/2017 4:24
C   2/25/2017 5:23  2/25/2017 5:24
D   2/22/2017 8:27  2/22/2017 8:28
A   2/24/2017 4:33  2/24/2017 4:34
B   2/23/2017 4:44  2/23/2017 4:44
C   2/17/2017 5:11  2/17/2017 5:11
D   2/14/2017 4:28  2/14/2017 4:28
A   2/16/2017 4:35  2/16/2017 4:35
B   2/13/2017 4:33  2/13/2017 4:33
C   2/11/2017 4:43  2/11/2017 4:43
D   2/10/2017 4:14  2/10/2017 4:14
A   2/9/2017 5:06   2/9/2017 5:06
B   1/20/2017 4:28  1/20/2017 4:29
C   2/6/2017 4:12   2/6/2017 4:12
D   2/4/2017 4:11   2/4/2017 4:12

Upvotes: 1

Views: 64

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

I am assuming that both start_date and end date are varchar2 columns.If not you may directly use the columns without the to_date function.

-- last 10 instances 
     SELECT job,
           Avg (To_date(end_date, 'MM/DD/YYYY HH24:MI:SS') -
                To_date(start_date, 'MM/DD/YYYY HH24:MI:SS')) * 1440 diff_in_minutes
    FROM   (SELECT job,
                   start_date,
                   end_date,
                   Row_number()
                     over (
                       PARTITION BY id
                       ORDER BY end_date DESC ) rn
            FROM   yourtable)
    WHERE  rn <= 10
    GROUP  BY job;

--Last 3 months    
    SELECT job,
           Avg (To_date(end_date, 'MM/DD/YYYY HH24:MI:SS') -
                To_date(start_date, 'MM/DD/YYYY HH24:MI:SS')) * 1440  diff_in_minutes
    FROM   yourtable
    WHERE  start_date > SYSDATE - interval '3' month
GROUP BY job;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, this is just an aggregation query with filtering:

select job, avg(end_date - start_date)
from t
where start_date > sysdate - interval '3' month
group by job;

Upvotes: 0

Related Questions