oracle - Need to update 15 min every last column value -
select route_desc, transit_time, to_timestamp(time_depart) + numtodsinterval(transit_time,'hour') "arrival", (to_timestamp(time_depart) +numtodsinterval(nvl(transit_time,0),'hour')) + interval '15' minute "dept" fe_jproute_ship_tmp trip_no='trp0000464' , guid='-1259520161' order seq_no;
i want output this
route_desc transit_time arraival dept ----------------------------------------------------------------- null null 25-jul-17 06.15.00.000000000 null 1 25-jul-17 07.15.00.000000000 25-jul-17 07.30.00.000000000 null 3 25-jul-17 10.30.00.000000000 25-jul-17 10.45.00.000000000
you need use cumulative sum()
- rows between unbounded preceding , current row
- default in oracle sum () on (order )
along nulls first
clause.
here simulation of query.
with fe_jproute_ship_tmp (select null route_desc, null transit_time, '25-jul-17 06.00.00.000000000 am' time_depart dual union select null route_desc, 1 transit_time, '25-jul-17 06.00.00.000000000 am' time_depart dual union select null route_desc, 3 transit_time, '25-jul-17 06.00.00.000000000 am' time_depart dual ) select transit_time, arrival, nvl2( arrival,arrival + interval '15' minute ,to_timestamp(time_depart ) + interval '15' minute ) "dept" (select transit_time, time_depart, to_timestamp(time_depart ) + numtodsinterval(cumu_transit_time,'hour') + numtodsinterval(15*cumu_dep_intv,'minute') arrival (select transit_time, time_depart, sum ( transit_time ) on ( order transit_time nulls first ) cumu_transit_time, sum ( nvl2(transit_time,1,null )) on ( order transit_time nulls first ) cumu_dep_intv fe_jproute_ship_tmp ) );
you have incorporate in main query. let me know feedback.
Comments
Post a Comment