Query help

[prodadmin]

orders
----------
orderid -Primkey
createddate

orderhistory
-------------
ordhistid
orderid -foriegnkey
orderstage(different stages for orders such as ordered,processed,mailed, delivered, reprocessed)
created

i would like to query count(orderid) orders created before 6months ago and need
count how many times it changed stage after first time it delivered.is this possible at all

example
-----------
orders
=============
3 - 12-DEC-2005

orders history
=================
11-3- ordered - 12-dec-2005
12-3-processed- 13-dec-2005
13-3-mailed - 14-dec-2005
14-3- delivered -15-dec-2005
15-3- reprocessed-11-jan-2006
16-3-mailed - 14-JAN-2006
17-3- delivered -17-JAN-2006

in this case it should give

orderid-count
==================
3-3

any help is much appreciated

[LKBrwn_DBA]

Try:

Select Count(Distinct Orderid) Orderid, Count(*) Stage_Count
  From Orders_History
 Where Trunc(Created) < Add_Months(Trunc(Sysdate),-6);

[prodadmin]

Thanks , i tried

but i would like to count the hits after it hit delivered first time

let's say

11-3- ordered - 12-dec-2005
12-3-processed- 13-dec-2005
13-3-mailed - 14-dec-2005
14-3- delivered -15-dec-2005
15-3- reprocessed-11-jan-2006
16-3-mailed - 14-JAN-2006
17-3- delivered -17-JAN-2006

for order 3 -it should display only 3
5-3- reprocessed-11-jan-2006
16-3-mailed - 14-JAN-2006
17-3- delivered -17-JAN-2006


also in system we have orders created before 12 months


your time is much appreciated

[LKBrwn_DBA]

Just add and group by orderid and orderstage to the query I posted.