I have a table which records episodes of clients going missing.
The table records (see fig1 of attached):
- the client ID and name,
- the episodes unique ID,
- the episode missing date/ time (episode start date),
- the date returned date/ time (episode end date),
- the number of hours the client was missing in the episode (returned date/ time - missing date/time)
- the number of times the client was missing in 28 days prior to the current episode (including the current episode).
The column I need some help to calculate is shown in fig2 named PREV_MIS_EPI_28Day_over72.
This column is a Y/N flag to flag if any of the previous episodes in the last 28 days (including the current) have lasted >=72 hours.
any help is appreciated.