Форум программистов [Powered by Invision Power Board]

Код


select Позиция, DT1,  DT2, sum(tab2.Штуки) from 
select Позиция, lag(DT) over (ORDER BY DT) DT1, DT DT2, tab2.Штуки from 
 select distinct Позиция,  DT from
 (
 select Позиция,  Дата1 DT from Таблица
 union
 select Позиция,  Дата2 DT from Таблица
 )
where Позиция<>lag(Позиция) over (ORDER BY DT)
) tab1
left join таблица tab2 on (tab2.Дата1 bettwen  tab1.DT1 and tab1.DT2) or (tab2.Дата2 bettwen  tab1.DT1 and tab1.DT2)
group by Позиция, DT1,  DT2

Код


with Table1 as (
select 1 position, TO_TIMESTAMP('15.01.2012 12:00','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 14:18','dd.mm.yyyy HH24:MI') DT2, 3 kols from dual union all                      
select 1 position, TO_TIMESTAMP('15.01.2012 13:27','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 14:00','dd.mm.yyyy HH24:MI') DT2, 2 kols from dual union all                      
select 1 position, TO_TIMESTAMP('15.01.2012 14:45','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 17:00','dd.mm.yyyy HH24:MI') DT2, 5 kols from dual union all                      
select 1 position, TO_TIMESTAMP('15.01.2012 16:00','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 17:00','dd.mm.yyyy HH24:MI') DT2, 1 kols from dual union all                      
select 2 position, TO_TIMESTAMP('15.01.2012 12:00','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 17:00','dd.mm.yyyy HH24:MI') DT2, 3 kols from dual union all                      
select 2 position, TO_TIMESTAMP('15.01.2012 14:00','dd.mm.yyyy HH24:MI') DT1, TO_TIMESTAMP('15.01.2012 17:44','dd.mm.yyyy HH24:MI') DT2, 3 kols from dual                      
)

select position, DT1, DT2, nvl(sum(kols),0) kols from 
( select tab1.position_old, tab1.position, tab1.DT1, tab1.DT2, tab2.DT1 tt, tab2.DT2 tt1, tab2.kols from 
   (select nvl(lag(position) over (PARTITION BY position ORDER BY DT),0) position_old, 
           position, lag(DT) over (PARTITION BY position ORDER BY DT) DT1, 
           DT DT2
             from 
              (select distinct position,  DT from
                ( 
                 select position,  DT1 DT from Table1
                 union
                 select position,  DT2 DT from Table1
                )
              ) 
  ) tab1
  left join Table1 tab2 on (tab2.DT1<tab1.DT2 and tab2.DT2>tab1.DT1) and tab2.position=tab1.position and tab2.position=tab1.position_old
 where tab1.dt1 is not null
 )
 group by position, DT1,  DT2 
 order by position, DT1