MySQL Forums
Forum List  »  MySQL Query Browser

Query Help Needed
Posted by: KG Muthukumar
Date: February 25, 2012 02:48PM

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

Options: ReplyQuote


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.