Calculate age, minus holding periods, in script

[#1]

Hi everyone,

ID	Status	Date
1	Open	1/1/2013
1	Changed from Open to Hold	1/15/2013
1	Changed from Hold to Open	2/1/2013
1	Changed from Open to Hold	2/15/2013
1	Changed from Hold to Open	3/1/2013
1	Changed from Hold to Closed	3/15/2013

What I want to do is Age(ID 1) = (3/15/2013 - 3/1/2013) + (2/15/2013 - 2/1/2013) + (1/15/2013 - 1/1/2013)

Basically, the item should not age if it is in a "Hold" state.

What's a good way of doing this?

Thank you

[pbaenen]

Can you just look at the previous status to determine the age? For example:

Status:

Load

          if(ID=Previous(ID) and Previous(Status) <> 'Changed from Open to Hold', Date-Previous(Date)) as AgeDays,

          *

          ;

LOAD * INLINE [

ID, Status, Date

1, Open, 1/1/2013

1, Changed from Open to Hold, 1/15/2013

1, Changed from Hold to Open, 2/1/2013

1, Changed from Open to Hold, 2/15/2013

1, Changed from Hold to Open, 3/1/2013

1, Changed from Hold to Closed, 3/15/2013

];