select min(t2.date_start) - t1.data_end
from 
    table t1, table t2 
where 
   t1.fk=t2.fk 
   and t2.date_start > t1.data_end
group by t1.data_end

select 
     w.fullname,
     wt.descr type,
     pr.name reason,
     count( 1 ) locations,
     sum(od.packs) packs,
     round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
     to_char(trunc(Sysdate) + sum(od.work_time)/( 24  *  60  *  60 ), 'HH24:MI:SS') sum_time,
     to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_loc,
     to_char(trunc(Sysdate) + (
     case 
          when sum(od.packs)= 0  
          then  0  
          else  (sum(od.work_time)/sum(od.packs))
     end
     )/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_pack
from 
     wms.oldpick_detail od, wms.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS') 
      and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
      and od.worker_id >  0 
--      and ol.reason = 'order_picking'
      and od.complete = 't'
      and w.id=od.worker_id
      and wt.name=od.opcode
      and not (
          od.opcode = 'ABSENCE'
          or (ol.reason = 'order_picking'
              and od.opcode = 'PICK')
              )
--      and od.opcode in ('FROM_CNT', 'FROM_PAL')
      and od.pick_id=ol.id
      and pr.id=ol.reason
group by (w.fullname,  wt.descr,  pr.name)

union

select 
     w.fullname,
     'Перемещение отобранной паллеты в ЕТ%' type,
     pr.name reason,
     count( 1 ) locations,
     sum(od.packs) packs,
     round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
     to_char(trunc(Sysdate) + sum(od.work_time)/( 24  *  60  *  60 ), 'HH24:MI:SS') sum_time,
     to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_loc,
     to_char(trunc(Sysdate) + (
     case 
          when sum(od.packs)= 0  
          then  0  
          else  (sum(od.work_time)/sum(od.packs))
     end
     )/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_pack
from 
     wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS') 
      and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
      and od.worker_id >  0 
      and ol.reason = 'order_picking'
      and od.opcode = 'PICK'
      and od.complete = 't'
      and od.order_id =  0 
      and w.id = od.worker_id
      and wt.name = od.opcode
      and od.pick_id = ol.id
      and pr.id = ol.reason
group by (w.fullname,  wt.descr,  pr.name)

union

select 
     w.fullname,
     'Перемещение монопаллеты в Е%' type,
     pr.name reason,
     count( 1 ) locations,
     sum(od.packs) packs,
     round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
     to_char(trunc(Sysdate) + sum(od.work_time)/( 24  *  60  *  60 ), 'HH24:MI:SS') sum_time,
     to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_loc,
     to_char(trunc(Sysdate) + (
     case 
          when sum(od.packs)= 0  
          then  0  
          else  (sum(od.work_time)/sum(od.packs))
     end
     )/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_pack
from 
     wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS') 
      and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
      and od.worker_id >  0 
      and ol.reason = 'order_picking'
      and od.opcode = 'PICK'
      and od.complete = 't'
      and od.order_id >  0 
      and w.id = od.worker_id
      and wt.name = od.opcode
      and od.pick_id = ol.id
      and pr.id = ol.reason
group by (w.fullname,  wt.descr,  pr.name)

union

select
    w.fullname,
    'Перемещение S->S%' type,
    pr.name reason,
    count( 1 ) locations,
    sum(od.packs) packs,
    round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
 --   lead (od.start_date,1, null) over (order by od.pick_id) d,
    case when lead (od.pick_id, 1 ) over (order by od.pick_id) = od.pick_id then
    to_char(trunc(Sysdate) + (((lead (od.start_date, 1 ) over (order by od.pick_id, od.start_date))-od.finish_date)), 'HH24:MI:SS')
    else 
    to_char(trunc(Sysdate) +  0 , 'HH24:MI:SS')
    end sum_time,
    to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_loc,
    to_char(trunc(Sysdate) + (
    case
         when sum(od.packs)= 0 
         then  0 
         else  (sum(od.work_time)/sum(od.packs))
    end
    )/( 24  *  60  *  60 ), 'HH24:MI:SS') time_one_pack

from
    wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS')
     and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
     and od.worker_id >  0 
     and ol.reason = 'order_picking'
  --   and od.opcode = 'PICK'
     and od.complete = 't'
     and (wt.name = 'FROM_CNT' or wt.name = 'FROM_PAL')
     and od.order_id >  0 
     and w.id = od.worker_id
     and wt.name = od.opcode
     and od.pick_id = ol.id
     and pr.id = ol.reason
group by w.fullname,  wt.descr,  pr.name, od.pick_id, od.start_date, od.finish_date

order by fullname
;