Analytics Software

I used sense of Tabibitosan B-)
870646

In this case,I imagine four traveler ;-)

create table t(prod,order_date,order_nbr) as
select 'ABC', to_date('6/12/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/13/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/14/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/15/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/16/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'ABC', to_date('6/17/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/20/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/21/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'ABC', to_date('6/22/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/18/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/19/2009','fmmm/dd/yyyy'), 1 from dual union all
select 'DEF', to_date('6/20/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/21/2009','fmmm/dd/yyyy'), 0 from dual union all
select 'DEF', to_date('6/22/2009','fmmm/dd/yyyy'), 0 from dual;

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
       Row_Number() over(partition by prod           order by order_date)
      -Row_Number() over(partition by prod,order_nbr order by order_date)
      as distance
        from T)
 where order_nbr = 1
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2

Hi,

Thanks for posting the CREATE TABLE and INSERT statements: that really helps!

Aketi seems to be assuming that order_nbr is always 0 or 1, and that the order_dates in your table are consecutive days (which happens to be true in your sample data).
If that's not the case, you can modify Aketi's solution like this:

WITH	got_grp	    AS
(
	SELECT	product_name
	,	order_date
	,	order_nbr
	,	ROW_NUMBER () OVER ( PARTITION BY  product_name
			      	     ORDER BY  	   order_date
				   ) + ( TRUNC (SYSDATE) 
				       - TRUNC (order_date)
				       )      		AS d_grp
	FROM	tt1
	WHERE	order_nbr	> 0
)
SELECT	  product_name
,	  MIN (order_date)	AS start_date
,	  COUNT (*)		AS consecutive_days
FROM	  got_grp
GROUP BY  product_name
,	  d_grp
ORDER BY  product_name
,     	  start_date
;

As written, this assumes that the combination (product_name, TRUNC (start_date)) is unique. If that's no the case, it can be adapted.

Oh I did not realize that I can use "where clause" in first select :-)
Likewise my previous solution,I imagine four traveler

select prod,min(order_date),count(*)
from (select prod,order_date,order_nbr,
      order_date-Row_Number() over(partition by prod order by order_date)
      as distance
        from T
       where order_nbr > 0)
group by prod,distance
order by min(order_date);

PRO  MIN(ORDE   COUNT(*)
---  --------  ---------
ABC  09-06-12          3
ABC  09-06-17          6
DEF  09-06-18          2

Thank you. I worked :)