Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query for Average time taken to process an Order?

681840Oct 28 2009 — edited Nov 3 2009
Hello,

I have following four tables.
/* There are other columns as well but just using the relevent columns.*/

CREATE TABLE COMPANY_ORDERS
(
  INTERNALORDERID            NUMBER(10)         NOT NULL,
  ORDERENTRYDATE             DATE,
  SYSTEMID             NUMBER(10)
)

/* There are other columns as well but just using the relevent columns.*/
/* The data in archive table goes after each three months.*/
CREATE TABLE COMPANY_ORDERS_ARCH
(
  INTERNALORDERID            NUMBER(10)         NOT NULL,
  ORDERENTRYDATE             DATE,
  SYSTEMID             NUMBER(10)
)

/* There are other columns as well but just using the relevent columns.*/
CREATE TABLE COMPANY_ORDER_HISTORY
(
  INTERNALORDERID   NUMBER(10)                  NOT NULL,
  ITEMORDER         NUMBER(10)                  NOT NULL,
  ENTRYDATE         DATE                        NOT NULL,
  UPDATEDATE        DATE,
  STATUSID          NUMBER(10),
  INTERNALSTATUSID  NUMBER(10)
)
/* The data in archive table goes after each three months.*/
CREATE TABLE COMPANY_ORDER_HISTORY_ARCH
(
  INTERNALORDERID   NUMBER(10)                  NOT NULL,
  ITEMORDER         NUMBER(10)                  NOT NULL,
  ENTRYDATE         DATE                        NOT NULL,
  UPDATEDATE        DATE,
  STATUSID          NUMBER(10),
  INTERNALSTATUSID  NUMBER(10)
)
The data for TABLE COMPANY_ORDER_HISTORY or TABLE COMPANY_ORDER_HISTORY_ARCH can be used as below:
SET DEFINE OFF;
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179058, 8, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179058, 10, TO_DATE('08/03/2009 11:42:16', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:16', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179180, 1, TO_DATE('08/03/2009 10:21:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:30', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179180, 3, TO_DATE('08/03/2009 10:25:06', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:06', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179180, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179180, 16, TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179184, 1, TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179184, 3, TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179184, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179184, 16, TO_DATE('08/03/2009 11:42:18', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:18', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179185, 1, TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:21:33', 'MM/DD/YYYY HH24:MI:SS'), -3, -3);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179185, 3, TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 10:25:09', 'MM/DD/YYYY HH24:MI:SS'), 0, 0);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179185, 14, TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:40:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 1360);
Insert into COMPANY_ORDER_HISTORY
   (INTERNALORDERID, ITEMORDER, ENTRYDATE, UPDATEDATE, STATUSID, INTERNALSTATUSID)
 Values
   (8179185, 16, TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/03/2009 11:42:17', 'MM/DD/YYYY HH24:MI:SS'), 7, 1430);
COMMIT;
Everyday a number of order is raised via different system (identified by COMPANY_ORDERS.SYSTEMID). Now I have to find per day average time an order took, per SYSTEMID to reach to a final statusid 7 (x), internalstausid 1430(y) from its first entrytime into COMPANY_ORDER_HISTORY (i.e For an orderid, the first entry in COMPANY_ORDER_HISTORY based in updatedate or For an orderid, or the entrytime for an orderid when ITEMORDER =1)

Archive tables can be joined only if the duration false in the period for which orders are archived.

Another suggestion seeked is Archive table is very big.
Will it be advisable to create a materialized view from the archived table having only last month data and then join the query to Materialized view (since the statistics needs to be shown only for last one year...)?

Regards

Edited by: A R on Oct 28, 2009 6:02 AM

Edited by: A R on Oct 28, 2009 2:13 PM
This post has been answered by Frank Kulash on Nov 2 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 1 2009
Added on Oct 28 2009
18 comments
2,107 views