Помогите решить задачку пожалуйста

Доброго времени суток.
Есть таблица с данными по наличию товара на отчетные даты:
(Ниже привожу пример на одном продукте)

PROD | Work_date
prod2 | 01.01.2015
prod2 | 01.04.2015
prod2 | 01.05.2015
prod2 | 01.06.2015
prod2 | 01.07.2015
prod2 | 01.09.2015
prod2 | 01.10.2015
prod2 | 01.11.2015
prod2 | 01.12.2015


Необходимо используя только SQL сформировать запрос, который должен сгруппировать данные по товарам и вывести интервалы наличия товаров
PROD|Date1 |Date2
prod2|01.01.2015|01.01.2015
prod2|01.04.2015|01.07.2015
prod2|01.09.2015|01.12.2015


Где Date1 и Date2 - начальная и конечная даты наличия товара которые определяются из поля Work_date приведенной выше таблицы

AndyPV,

Подсказка: группируй по PROD, WORK_DATE - ROW_NUMBER() OVER(PARTITION BY PROD ORDER BY WORK_DATE).

SY.

Спасибо, буду пробовать

with recs as (select * from
(
  select 'prod2' pr, to_date('01.01.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.04.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.05.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.06.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.07.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.09.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.10.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.11.2015', 'dd.mm.yyyy') dt from dual union all
  select 'prod2' pr, to_date('01.12.2015', 'dd.mm.yyyy') dt from dual
))
select
        pr,
        min(dt) dt,
        max(dt1) dt1
from (        
select 
        lv,
        lf,
        rt,
        pr,
        dt,
        case when lf=1 and dl>1 then dt else dt1 end dt1,
        dl
from (
select 
        level lv, 
        connect_by_isleaf lf, 
        connect_by_root dt rt, 
        t.* 
from (
select 
        pr,
        dt,
        dt1,
        months_between(dt1, dt) dl
from (
select 
        pr,
        dt,
        (select min(r1.dt) from recs r1 where r1.pr=r.pr and r1.dt>r.dt) dt1
from 
        recs r
)
) t
connect by 
        prior dt=dt1 
        and dl=1 
        start with dl is null or dl>1
)
where
      not (lf=0 and dl>1)
)
group by
          pr,
          rt      
order by
          2
;

to intrilligator,

+

intrilligator with recs as (select * from ( select 'prod2' pr, to_date('01.01.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.04.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.05.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.06.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.07.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.09.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.10.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.11.2015', 'dd.mm.yyyy') dt from dual union all select 'prod2' pr, to_date('01.12.2015', 'dd.mm.yyyy') dt from dual union all select 'prod1' pr, to_date('01.10.2015', 'dd.mm.yyyy') dt from dual union all select 'prod1' pr, to_date('01.12.2015', 'dd.mm.yyyy') dt from dual )) select pr, min(dt) dt, max(dt1) dt1 from ( select lv, lf, rt, pr, dt, case when lf=1 and dl>1 then dt else dt1 end dt1, dl from ( select level lv, connect_by_isleaf lf, connect_by_root dt rt, t.* from ( select pr, dt, dt1, months_between(dt1, dt) dl from ( select pr, dt, (select min(r1.dt) from recs r1 where r1.pr=r.pr and r1.dt>r.dt) dt1 from recs r ) ) t connect by prior dt=dt1 and dl=1 start with dl is null or dl>1 ) where not (lf=0 and dl>1) ) group by pr, rt order by 2 ;

AndyPV,

у тебя даты в каком формате? dd.mm.yyyy или как у наших заокеанских товарищей - mm.dd.yyyy

intrilligator,

сильно сложно и логически неверно. особенно, с учетом прозвучавшей подсказки от SY

AndyPV,

считаем, что даты нашинские, то есть dd.mm.yyyy:

--
-- Тестовые данные:
with t as
(
  select 'prod2' prod, to_date('01.01.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.04.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.05.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.06.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.07.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.09.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.10.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.11.2015', 'dd.mm.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.12.2015', 'dd.mm.yyyy') Work_date from dual
)
--
-- Основной запрос:
select prod, min(Work_date) as date1, max(Work_date) as date2
  from (
         select prod, Work_date
              , row_number() over(partition by prod order by Work_date) -
                extract(month from Work_date) as grp_id
           from t
       )
 group by prod, grp_id
 order by prod, min(Work_date);

on-line проверка на sqlfiddle.com

AndyPV,

для американского формата дат всё ещё проще:

--
-- Тестовые данные:
with t as
(
  select 'prod2' prod, to_date('01.01.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.04.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.05.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.06.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.07.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.09.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.10.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.11.2015', 'mm.dd.yyyy') Work_date from dual union all
  select 'prod2' prod, to_date('01.12.2015', 'mm.dd.yyyy') Work_date from dual
)
--
-- Основной запрос:
select prod, min(Work_date) as date1, max(Work_date) as date2
  from (
         select prod, Work_date
              , Work_date - row_number() over(partition by prod order by Work_date) as grp_id
           from t
       )
 group by prod, grp_id
 order by prod, min(Work_date)

on-line проверка sqlfiddle.com

Добрый Э - Эх,

Вери биг сенькс. Вчера весь день посвятил изучению аналитических функций, но ничего не смог сделать... Буду пробовать