Hi,
I have two tables as follows,
Is there a way to seperate the average time in Days, Hours, Mins, Sec.
I need the average time taken for each account.
with
table1 as
(select 'AAA' acname,123 tid,to_date('01-11-15 13:00','mm-dd-yy hh24:mi:ss') ltime from dual union all
select 'AAA',456,to_date('01-11-15 15:00','mm-dd-yy hh24:mi:ss') from dual union all
select 'BBB',789,to_date('01-11-15 04:00','mm-dd-yy hh24:mi:ss') from dual union all
select 'CCC',990,to_date('01-11-15 18:00','mm-dd-yy hh24:mi:ss') from dual union all
select 'BBB',880,to_date('01-11-15 05:00','mm-dd-yy hh24:mi:ss') from dual union all
select 'DDD',770,to_date('01-11-15 05:00','mm-dd-yy hh24:mi:ss') from dual union all
select 'BBB',555,to_date('01-11-15 13:00','mm-dd-yy hh24:mi:ss') from dual
),
table2 as
(select 123 txid,to_date('01-11-15 14:00','mm-dd-yy hh24:mi:ss') mtime from dual union all
select 456,to_date('01-11-15 16:00','mm-dd-yy hh24:mi:ss') from dual union all
select 789,to_date('02-11-15 04:00','mm-dd-yy hh24:mi:ss') from dual union all
select 770,to_date('02-11-15 07:00','mm-dd-yy hh24:mi:ss') from dual union all
select 990,to_date('01-11-15 23:30','mm-dd-yy hh24:mi:ss') from dual union all
select 880,to_date('02-11-15 05:00','mm-dd-yy hh24:mi:ss') from dual union all
select 555,to_date('02-11-15 13:00','mm-dd-yy hh24:mi:ss') from dual
)
select acname,avg(hrs)
from (select distinct t1.acname,(t2.mtime - t1.ltime)*24 hrs
from table1 t1,
table2 t2
where t1.tid = t2.txid
)
group by acname
Expected Output
Acname Avg(day) Avr (hrs) Avg (Mins) Avg (Secs)
AAA 0 1 0 0 (i.e 14:00 - 13:00=1, and 16-15=1, Avg = (1+1)/2 =1)
BBB 1 0 0 0
CCC 0 5 30 0
DDD 1 2 0 0