Hello Members,
I am trying to find the average of Time Difference of two timestamp columns by order number. I need to show the average of the time difference in hours:mins format. I have is SQL Server , but unable to get this in Oracle SQL. Could anyone please let me know how I need to find the time difference of two timestamp columns and show them in hh:mm fomat and then take average of these differences and show them in hh:mm format. Below is what I have tried in SQL server.
CREATE TABLE temp
(
startdate smalldatetime,
enddate smalldatetime
)
INSERT temp
SELECT '6/22/2013 19:00',
'6/22/2013 19:20'
UNION ALL
SELECT '6/22/2013 19:22',
'6/22/2013 19:44'
UNION ALL
SELECT '6/22/2013 19:45',
'6/22/2013 19:50'
UNION ALL
SELECT '6/22/2013 19:52',
'6/22/2013 20:02'
UNION ALL
SELECT '6/22/2013 20:05',
'6/22/2013 20:10'
UNION ALL
SELECT '6/22/2013 20:15',
'6/22/2013 20:20'
SELECT CAST
(
CAST
( SUM
(
DATEDIFF(MINUTE,startdate,enddate)
)
/
(
6*60
)
As float)
as varchar) + ':' +
cast
(
cast
(
cast
(
(
SUM
(
DATEDIFF(MINUTE,startdate,enddate)
)
/
6
)
as int )
% 60 as float )
as varchar ) AS avg_time_diff_in_hh_mm_format
FROM temp
Please let me know if you need additional information.
Thanks in Advance.