Check for change in status between records

[cianw]

I have a table which contains multiple records per ID. A new record is entered each time one of the fields change. This can include changes to the STATUS field (a two letter field eg IR).
When a change occurs a new row is created with a datastamp of the time of change.

I want to for each ID track the changes to the STATUS field in the order they occured and when they occurred and generate one record for each ID.

eg orignal table

ID STATUS DATA_CHANGED
10001 QA 1/1/2003
10001 QA 2/1/2003
10001 PO 3/1/2003

and convert to
ID QA PO PATH
10001 1/1/203 3/1/2003 QA_PO


Any ideas?

 

[rac2]

There is no general solution to this problem in SQL. Queries can be written for a fixed number of occurrances, current, one-back, two-back, three-back, etc. The result will always have the fixed number of columns, some will be NULL or have a value you choose to display for null. If you can live with that then the solution is to self-join subsets of the table.

In this particular case, the solution is even more difficult because the subset needed in each join depends on a relationship between rows, DATA_CHANGED in the row from a subset is less than DATA_CHANGED in the row from the other subset. If the rows had occurance values, 1, 2, 3, 4, etc. the solution would be simplified because you could define subsets based on a known value.

It may be apparent at this point that I am not going to provide a SQL solutuion to your problem.

Are you using a system that has a facility for stored procedures? If so then I think that is the way to go. Write a procedure that uses a cursor to move through the rows in order. Build a string for each ID, then save it in a temporary table. When all rows are processed, SELECT * FROM the temporary table and you will have the result you need.

 

[cianw]

just managed to do it in SQL. Using the new Oracle 8i Analysis functions (LAG, PARITION_BY, ROW_NUMBER ) it whips through and creates a table in a minute.

 

[carp]

Please post the SQL - this would be useful to a lot of forum members!

 

[cianw]

no probs. I'm am cr&pping my pants about how godly the new functions are. Here is a link describing the new functions

http://www.akadia.com/services/ora_analytic_functions.html#Crosstab or Pivot Queries

One piece of sql with no selfjoins compares each record for an ID for changes in a field and based on the dates created a PATH for the order of the status. You need 8i or above.

create table path_test as
(select b. ID,
min(case when b.STATUS = 'QA' then b.DATA_CHANGED else null end) QA,
min(case when b.STATUS = 'PO' then b.DATA_CHANGED else null end) PO,
max(case when b.seq =1 then b.STATUS else null end)||max(case when b.seq =2 then '_'||b.STATUS else null end)||max(case when b.seq =3 then '_'||b.STATUS else null end)||max(case when b.seq =4 then '_'||b.STATUS else null end)||max(case when b.seq =5 then '_'||b.STATUS else null end)||max(case when b.seq =6 then '_'||b.STATUS else null end)||max(case when b.seq =7 then '_'||b.STATUS else null end)||max(case when b.seq =8 then '_'||b.STATUS else null end)||max(case when b.seq =9 then '_'||b.STATUS else null end)||max(case when b.seq =10 then '_'||b.STATUS else null end)||max(case when b.seq =11 then '_'||b.STATUS else null end) PATH
from
(select a.id,a.STATUS,DATA_CHANGED, row_number() over (PARTITION BY ID order by a.DATA_CHANGED) seq
from -- select those which are a status change and add row number sequence)
(select aud_app_id,STATUS,DATA_CHANGED, -- compare each app_id record to previous ordered by date
LAG(STATUS,1,0)
OVER (PARTITION BY aud_app_id ORDER BY DATA_CHANGED) AS NEW1
FROM BASETABLE) a
where a.NEW1 <> a.STATUS) b
group by b.ID)