MySQL Forums
Forum List  »  MySQL Query Browser

Re: Query Help Needed
Posted by: irek kordirko
Date: February 26, 2012 10:39AM

It would be very helpful if you provided sample data in a form of CREATE TABLE and INSERT statements.
This greatly increases your chance to getting help, since it allows others to simply recreate a test case on their computers with use of a copy-paste command.
People like to puzzle with challenging problems, but don't like to tiresomely amend sample data adding sql commands, commas and apostrophes.

A desired and welcome format is like this:
create table source_table_example(
Row_Seq_Id int,
Order_Id int,
Status_Id varchar(100),
Status_Create_Datetime datetime
);

insert into source_table_example
select 1001, 5001, 'CREATE',  '2012-02-13 10:15:37' union
select 1005, 5001, 'PROCESS', '2012-02-13 11:15:37' union
select 1007, 5001, 'APPROVED', '2012-02-13 11:17:47' union
select 1008, 5001, 'PROCESS', '2012-02-13 11:17:57' union
select 1010, 5001, 'SHIPPED', '2012-02-13 14:17:47' union
select 1015, 5001, 'COMPLETE', '2012-02-14 10:15:37' union
select 1003, 5002, 'CREATE', '2012-02-13 10:25:37' union
select 1006, 5002, 'PROCESS', '2012-02-13 11:16:17' union
select 1009, 5002, 'APPROVED', '2012-02-13 14:27:47' union
select 1011, 5002, 'SHIPPED', '2012-02-13 18:15:37' union
select 1014, 5002, 'COMPLETE', '2012-02-13 20:15:37' ;


And a query for that data would be like this one:
SELECT Row_Seq_Id, 
       Order_Id,
       Status_Id,
       Status_Create_Datetime,
       CASE WHEN next_rowseqid IS NULL
          THEN '9999-99-99 99:99:99'
          ELSE ( SELECT Status_Create_Datetime
                 FROM source_table_example ste
                 WHERE ste.Row_Seq_Id = alias.next_rowseqid
               )
        END Status_end_datetime,
        CASE WHEN next_rowseqid IS NULL
             THEN 1 ELSE 0
        END curr_id
FROM (
    SELECT  Row_Seq_Id, 
            Order_Id,
            Status_Id,
            Status_Create_Datetime,
            (   SELECT min( row_seq_id)
                FROM source_table_example ste1
                WHERE ste1.order_id = ste.order_id
                    AND ste1.row_seq_id > ste.row_seq_id
                GROUP BY ste.order_id
            ) next_rowseqid
    FROM source_table_example ste
) alias
;

+------------+----------+-----------+------------------------+---------------------+---------+
| Row_Seq_Id | Order_Id | Status_Id | Status_Create_Datetime | Status_end_datetime | curr_id |
+------------+----------+-----------+------------------------+---------------------+---------+
|       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 |       0 |
|       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 |
+------------+----------+-----------+------------------------+---------------------+---------+
11 rows in set (0.00 sec)



Edited 4 time(s). Last edit at 02/26/2012 10:43AM by irek kordirko.

Options: ReplyQuote


Subject
Written By
Posted
February 25, 2012 02:48PM
Re: Query Help Needed
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.