with interval_calling
as
(select stn,rdate, (SELECT CAST(CALL_ENDTIME AS TIME)
FROM bcm_temp DD
WHERE rownum = 1
and D.STN = DD.STN
AND DD.rec_id > D.rec_id
AND DD.call_endtime > D.call_endtime) -
(SELECT CAST(CALL_ENDTIME AS TIME)
FROM bcm_temp DD
WHERE D.rec_id = DD.rec_id
AND D.STN = DD.STN
and D.call_endtime = DD.call_endtime) as interv,
SUM(SECONDS) totaltimeinsec
,(((sum(seconds))/28800)*100) avgworkingratio,

((28800-(sum(seconds)))/28800)*100 avgidleratio,

(sum(seconds))/(count(seconds)) AVGCALL_DURATION,

count(seconds) as totalcalls,
sum(case when seconds <=60 then 1 else 0 end) as total_lessmin1_Call,

sum(case when seconds between 61 and 180 then 1 else 0 end) as total_min3_call,

sum(case when seconds >180 then 1 else 0 end) as total_longcalls,

to_char(trunc(sum(seconds)/60/60),'09') ||':'||
to_char(trunc(mod(sum(seconds),3600)/60),'09') ||':'||
to_char(mod(mod(sum(seconds),3600),60),'09')
as "totallcall_in_HH:MM,SS"
from bcm_temp d
group by stn,rdate,rec_id,call_endtime
)


select stn,rdate,
case when interv< numtodsinterval(0,'second') then numtodsinterval(0,'second')
when interv > numtodsinterval(0,'second') then interv
else numtodsinterval(0,'second')
end
from interval_calling

if i put order by clause in the end ( order by stn )

the execution time leaps up from 0.007 sec to 89 sec . ( its underdeveloping)