top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Tracking currrent and previous status

0 votes
51 views

I have a table which keeps the status and created_timestamp corresponding to a process_id. One row is inserted as soon as the process status changes. So, there are as many rows with same process_id as there are statuses associated with it. I want to create another table/view using this data which has one single row corresponding to a process_id, its current status and its previous status. I need to make a Informatica job for this, but a SQL query will be just as helpful.
Sample Input:

Process_id | Status     | Created 
         1 | In_queue   | 2014-08-01 00:01:01 
         1 | Started    | 2014-08-01 01:03:01 
         1 | In_process | 2014-08-01 01:50:20 
         1 | Complete   | 2014-08-01 03:10:20 

Sample Output: 
Process_id | Previous_status | Current_status | Updated 
         1 | In_process      | Complete       | 2014-08-01 03:10:20
posted Aug 5, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Performance aside, here's how I'd do it...

SELECT a.process_id
     , a.status
     , a.created
     , b.status prev_status
     , b.created prev_created
  FROM 
     ( SELECT x.*
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.process_id = x.process_id
          AND y.created >= x.created
        GROUP
           BY x.process_id
            , x.created
     ) a
  LEFT
  JOIN
     ( SELECT x.*
            , COUNT(*) rank
         FROM my_table x
         JOIN my_table y
           ON y.process_id = x.process_id
          AND y.created >= x.created
        GROUP
           BY x.process_id
            , x.created
     ) b
    ON b.process_id = a.process_id
 WHERE b.rank = a.rank + 1
   AND a.rank = 1;
answer Aug 7, 2014 by Shweta Singh
Similar Questions
+1 vote

Hi,
I have a scenario, where in we have a single record and multiple columns like this

Record numer cd1 cd2 cd3 cd4 cd5 cd6 cd7 cd8 cd9
123 12 null 13 14 null 15 16 17 null
Here we have value for cd1 and not for cd2 and we have value for cd3 so cd2 is empty so cd3 should get into cd2 since it was empty so we should move the next available values to previous available spaces.

Does anyone know how to achieve this scenario?

+1 vote

Is it possible to get the name of previous task from current task in in particular workflow in informatica?
Is there any environment variable which holds the value of task ran?

+2 votes

Is there any environment variable which holds the value of task ran ?

0 votes

I need to show the latest Status based on 2 attributes (LAST_UPDATE and STAUS)
How can I do it in informatica? the source is flat file

Example:

NUMBER --------------------LAST_UPDATE ----------------- STATUS  
-----1 -----------------------01/26/2015  ----------------------   CREATED                  
-----1 ----------------------   01/27/2015  ------------------UNDER_PROCCESS                           
-----1----------------------    01/28/2015  ---------------------COMPLETED  
 -----2---------------------- 01/28/2015 ------------------ CREATED  
-----3---------------------- 01/28/2015 --------------------- UNDER_PROCCESS

Result should be

NUMBER --------------------LAST_UPDATE ------------- STATUS  ---------------LAST_STAUS  
-----1 -----------------------01/26/2015  ----------------------   CREATED -----------COMPLETED                 
-----1 ----------------------   01/27/2015  -----------------UNDER_PROCCESS ---- COMPLETED                           
-----1----------------------    01/28/2015  ---------------------COMPLETED ----------COMPLETED  
 -----2---------------------- 01/28/2015 ------------------ CREATED  ---------------- CREATED  
-----3---------------------- 01/28/2015 -------------UNDER_PROCCESS --UNDER_PROCCESS   
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...