Query Help Needed
I've a question with respect to a SQL Query and would appreciate any solution that you may be able to provide.
I am looking to do the following:
SOURCE_TABLE structure
ROW_SEQ_ID (An ascending seqno uniquely identifying each row in the table, not necessarily contiguous)
ORDER_ID
STATUS_ID
STATUS_CREATE_DATETIME
TARGET_TABLE structure
ROW_SEQ_ID
ORDER_ID
STATUS_ID
STATUS_CREATE_DATETIME
STATUS_END_DATETIME (this date time should be the STATUS_CREATE_DATETIME of the next status for the same ORDER_ID - i.e STATUS_CREATE_DATETIME of the higher ROW_SEQ_ID record for the same ORDER_ID)
CURR_IN (0 or 1 value indicating the which row is the current/latest STATUS_ID for an ORDER_ID )
SOURCE_TABLE Example
Row_Seq_Id Order_Id Status_Id Status_Create_Datetime
1001 5001 CREATE 2012-02-13 10:15:37
1005 5001 PROCESS 2012-02-13 11:15:37
1007 5001 APPROVED 2012-02-13 11:17:47
1008 5001 PROCESS 2012-02-13 11:17:57
1010 5001 SHIPPED 2012-02-13 14:17:47
1015 5001 COMPLETE 2012-02-14 10:15:37
1003 5002 CREATE 2012-02-13 10:25:37
1006 5002 PROCESS 2012-02-13 11:16:17
1009 5002 APPROVED 2012-02-13 14:27:47
1011 5002 SHIPPED 2012-02-13 18:15:37
1014 5002 COMPLETE 2012-02-13 20:15:37
TARGET_TABLE
Row_Seq_Id Order_Id Status_Id Status_Create_Datetime Status_end_datetime CURR_IN
1001 5001 CREATE 2012-02-13 10:15:37 2012-02-13 11:15:37 0
1005 5001 PROCESS 2012-02-13 11:15:37 2012-02-13 11:17:47 0
1007 5001 APPROVED 2012-02-13 11:17:47 2012-02-13 11:17:57 0
1008 5001 PROCESS 2012-02-13 11:17:57 2012-02-13 14:17:47 0
1010 5001 SHIPPED 2012-02-13 14:17:47 2012-02-14 10:15:37
1015 5001 COMPLETE 2012-02-14 10:15:37 9999-99-99 99:99:99 1
1003 5002 CREATE 2012-02-13 10:25:37 2012-02-13 11:16:17 0
1006 5002 PROCESS 2012-02-13 11:16:17 2012-02-13 14:27:47 0
1009 5002 APPROVED 2012-02-13 14:27:47 2012-02-13 18:15:37 0
1011 5002 SHIPPED 2012-02-13 18:15:37 2012-02-13 20:15:37 0
1014 5002 COMPLETE 2012-02-13 20:15:37 9999-99-99 99:99:99 1
Note:
1. The statuses are not necessarily consecutive; an order can go from PROCESS to APPROVED to PROCESS back again or other such forward-backward combinations.
2. The Status_end_datetime of a record is the Status_Create_Datetime of the next status record for the same order.
Both a CURSOR and a NON-CURSOR solution are welcome..
thanks
KG
Subject
Written By
Posted
Query Help Needed
February 25, 2012 02:48PM
February 26, 2012 10:39AM
February 26, 2012 06:46PM
February 27, 2012 02:18PM
February 27, 2012 08:14AM
February 27, 2012 05:05PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.