Deploying duration models using timepoint borders
Although using timepoints in the system makes it possible to model the elapsed time duration, border identification can be demanding and too time-consuming; therefore, it is necessary to find the direct next state. The processing takes place in two phases. Firstly, the states where the validity begins after a defined timepoint (T) are identified, then the state with the lowest value of the beginning of validity is selected from this set. OBJ_REF expresses the referenced object identifier:
- First phase:
SELECT Tab.*, RANK() OVER(ORDER BY BD) as rank FROM Tab WHERE BD>T and ID=OBJ_REF;
- Second phase:
SELECT * FROM (SELECT Tab.*, RANK() OVER(ORDER BY BD) as rank FROM Tab WHERE BD>T and ID=OBJ_REF ) WHERE rank=1;
Note that a solution where only the end date of the validity (state expiration...