Hello ,
I need some help for a possible solution.
I have a simple table with 6 columns of interest:
- subject
- stay_id
- date_time_event
- start_time
- stop_time
- value
Possible values can be:
1 1000 2020-09-10 23:43:00 2020-09-10 20:00:00 2020-09-18 20:00:00 0.7
1 1000 2020-09-11 03:26:00 2020-09-10 20:00:00 2020-09-18 20:00:00 1
1 1001 2020-09-14 02:01:00 2020-09-12 12:52:00 2020-09-21 19:36:00 2.1
3 1002 2020-10-11 15:45:00 2020-10-01 12:51:00 2020-11-21 20:00:00 0.9
3 1002 2020-10-28 10:34:00 2020-10-01 12:36:00 2020-11-21 20:00:00 0.8
4 1003 2020-11-11 15:41:00 2020-11-01 12:00:00 2020-11-20 20:00:00 2.7
The purpose is to calculate if there’s an increase of the value of at least 0.3 within intervals of 48h.
So basically I want to group by (subject and stay_id) and then I would like to apply a Window Loop Start time-based (window of 48 hours and step size of 1 hour) verifying that all values retrieved in that 48h satisfy the condition max(value_within_48h) - min(value_within_48h) >= 0.3
The loop should start when date_time_event >= start_time and stop when it is <= out_time.
What I have done til now is to Group By subject and stay_id and List(sort) all date_time_event per group. It would be great now to apply the Window Loop Start on this list, but this can’t be done because of the different type.
The moving window should verify if the increase has occurred, once this is TRUE, I put a new column FLAG with value 1.
What I should output is the following:
subject stay_id date_time_event flag
1 1000 2020-09-11 03:26:00 1
3 1002 2020-10-11 15:45:00 0
4 1003 2020-11-11 15:41:00 0
Thank you for any ideas.