Roster to roster 2 selection based on latest event

Good evening,

Questions about subselecting events from the roster for another roster. We are adapting the paper version, which requires asking questions only to the last and pre-last entry.

Roster1 contains information on: Child name, pregnancy duration, and date of birth, etc. Question in Roster2 should only be asked to the last and ideally pre-last pregnancy based on the date of birth.

While it is easy to pool from roster1 based on the combination of duration and year (for example, lasted 8 months and during last 5 years), I am not sure how to pool based on the order of the date. You previously mentioned that sorting of the whole roster is not possible.

Looking at similar issues, like

Would the solution be: for each entry in the first roster calculate the N days between two dates (based on current date and the birth date) and then select the minimum value? Is there a function for that? MIN? Is there a way to select next smallest to it? Or is there a simpler way to identify the most recent date and/or the date next to it? I suppose it is somewhat similar to selecting the oldest person in the household, and treating date not as a date but as N of days in between dates.

Any suggestion are very appreciated.
Thank you,

will be true for the last birth in the birth histories. subtract 2 for pre-last.

Best, Sergiy Radyakin


since you are interested in the last chronological birth, you can order the births chronologically before making a selection.
Use the OrderBy() function.
See details here:

Best, Sergiy Radyakin

Dear Sergiy,

Just to clarify, the births are not always reported in the chronological order (as women can forget and then recall the event), but we need to ask (select) the last birth in all rosster based on the reported date of birth, not the sequence number how it was recorded - any suggestions?