When working with an event history in an expression, it often makes the expression much simpler (and easier to understand) if you create an effective date array to hold all the stop event dates, e.g., StopEventDates.

For example, something like (#DATE >= StartEventDates) #AND (#DATE <= StopEventDates) can be very useful. It will also make things much easier if the final decrement date (e.g., #DODEC) is in StopEventDates when the final event is not a stop event. Remember that when performing a calculation, often the final decrement date isn’t in the event/status history because the participant hasn’t actually terminated or retired yet (so it's not in the database's event history).

First, you need to create an effective date array field in the Data Dictionary to hold the stop event dates (e.g., StopEventDates).

Now, create a Data Default for StopEventDates (always apply the default) using this expression. This expressions assumes that the event history is in a coded effective date array called EventHistory and that the only start event codes are 10, 11, and 12, and there are only start & stop event codes in the event history. (You can always change from #NOTIN to #IN and list all the stop events instead.) 

 

Eff_DT:= #START EventHistory &

Code:= #VALUE EventHistory &
IsStopEvent:= Code #NOTIN (10,11,12) & ; if you change the codes here, check the StartEventHistory default
 
; if the last event is not a stop event, then we need to add #DODEC as the last stop event
AddDODEC:= 0 = (Eff_dt #GETVALUE IsStopEvent) &
; First, #GETVALUE finds the last non-zero value in Eff_DT (it's always the last one), and then
; grabs the corresponding value in IsStopEvent. (Without a left arg, #GETVALUE IsStopEvent returns
; the last non-zero value in IsStopEvent, which is not what we want.) Now, if the last value in
; IsStopEvent is a zero (i.e., a start event), then indicate we need to add #DODEC to the event
; history.
 
PriorEff_DT:= -1 #START EventHistory &
; For each stop event, PriorEff_DT contains the previous start date. We want the effective date for
; each stop date in the final array to be the previous start event date. That way when we use
; StartEventDates and StopEventDates in an expression, the corresponding start and stop event dates
; will always be available at the same time (calculation dates).
 
12 #ARRAY (PriorEff_DT * IsStopEvent, Eff_DT, AddDODEC * #GETVALUE Eff_DT, #DODEC)
; (1) #ARRAY always drops rows with a zero effective/start date from the final array
; (2) All the effective dates for stop events have been multiplied by zero (see IsStartEvent above), so
;     #ARRAY will drop them from the final array, leaving just start event dates in the final array
; (3) If the last event in the event history is a start event, then add the decrement date