Forum Discussion
Calculating Time Difference - Multiple Start point and Multiple End Point for Alarm in same column
Good afternoon All,
I need some help in order to resolve bit of tricky calculation. We have Nurse Call System in place where it collects the data from various Health Care Unit (Rooms). The trigger point and Trigger location panel are dotted around the building Normally, there is two events. Call (Start of Event) or Assistance (Start of Event) to trigger notification to all health care assistant and nurses that Patient in care require assistance. Depending on situation and location, health care assistant or nurses present in building either reset alarm trigger or press Present or Accepted (For allocation and go to physically in room before Resetting the call.). So, in a way, it is multiple start point and multiple end points. In this way, the Start of the event is always Call but end of event could be Reset, Accepted, Present.
To regulate and monitor staff efficiency, we would like to find out time duration from Call to any of this event.
Below is the sample of data
02/01/2018 09:58 | Bedroom 46 U/G | Assistance |
02/01/2018 09:37 | Bedroom 4 L/G | Reset |
02/01/2018 09:37 | Bedroom 4 L/G | Call |
02/01/2018 09:04 | Bedroom 15 L/G | Reset |
02/01/2018 08:52 | Bed 69 Poppy | Present |
02/01/2018 08:52 | Bed 69 Poppy | Assistance |
02/01/2018 08:42 | Bedroom 3 L/G | Reset |
02/01/2018 08:42 | Bedroom 3 L/G | Present |
02/01/2018 08:41 | Bedroom 3 L/G | Assistance |
02/01/2018 08:39 | Bedroom 5 L/G | Reset |
02/01/2018 08:38 | Bedroom 5 L/G | Call |
02/01/2018 08:37 | Bedroom 16 L/G | Reset |
02/01/2018 08:35 | Bedroom 5 L/G | Reset |
02/01/2018 08:35 | Bedroom 5 L/G | Assistance |
02/01/2018 08:00 | WC Poppy | Present |
02/01/2018 07:58 | WC Poppy | Accept |
02/01/2018 07:58 | WC Poppy | Call |
02/01/2018 07:56 | WC Poppy | Accept |
02/01/2018 07:55 | WC Poppy | Call |
02/01/2018 07:28 | Bedroom 28 L/G | Present |
02/01/2018 07:27 | Bedroom 28 L/G | Accept |
02/01/2018 04:29 | Bedroom 16 L/G | Assistance |
02/01/2018 04:27 | Bedroom 16 L/G | Accept |
02/01/2018 04:27 | Bedroom 16 L/G | Assistance |
02/01/2018 03:41 | Bedroom 36 U/G | Present |
02/01/2018 03:40 | Bedroom 36 U/G | Accept |
02/01/2018 03:40 | Bedroom 36 U/G | Assistance |
02/01/2018 01:26 | Bedroom 40 U/G | Present |
02/01/2018 01:26 | Bedroom 40 U/G | Reset |
02/01/2018 01:25 | Bedroom 40 U/G | Accept |
02/01/2018 01:24 | Bedroom 40 U/G | Call |
02/01/2018 00:53 | Bedroom 43 U/G | Present |
02/01/2018 00:10 | Bed 69 Poppy | Present |
02/01/2018 00:10 | Bed 69 Poppy | Assistance |
02/01/2018 00:10 | Bedroom 36 U/G | Present |
02/01/2018 00:09 | Bedroom 36 U/G | Accept |
02/01/2018 00:09 | Bedroom 36 U/G | Call |
01/01/2018 23:49 | Bedroom 36 U/G | Reset |
01/01/2018 23:49 | Bedroom 36 U/G | Present |
01/01/2018 23:48 | Bedroom 36 U/G | Accept |
01/01/2018 23:48 | Bedroom 36 U/G | Assistance |
5 Replies
I have some question regarding your data set:
Bedroom 15, how can you have one Reset without any call / assistance ?
Time Stamp Location Signal 2/1/2018 9:04 Bedroom 15 L/G Reset Bedroom 28, same scenario:
Time Stamp Location Signal 2/1/2018 7:27 Bedroom 28 L/G Accept 2/1/2018 7:28 Bedroom 28 L/G Present
Bedroom 43, same scenario:
Time Stamp Location Signal 2/1/2018 0:53 Bedroom 43 U/G Present Bedroom 3, you have one call / assistance, followed by multiple accept present, which of the two is relevant and why, Reset or Present?
Time Stamp Location Signal 2/1/2018 8:41 Bedroom 3 L/G Assistance 2/1/2018 8:42 Bedroom 3 L/G Reset 2/1/2018 8:42 Bedroom 3 L/G Present same goes for bedroom 36:
Time Stamp Location Signal 1/1/2018 23:48 Bedroom 36 U/G Assistance 1/1/2018 23:48 Bedroom 36 U/G Accept 1/1/2018 23:49 Bedroom 36 U/G Reset 1/1/2018 23:49 Bedroom 36 U/G Present 2/1/2018 0:09 Bedroom 36 U/G Call 2/1/2018 0:09 Bedroom 36 U/G Accept 2/1/2018 0:10 Bedroom 36 U/G Present 2/1/2018 3:40 Bedroom 36 U/G Assistance 2/1/2018 3:40 Bedroom 36 U/G Accept 2/1/2018 3:41 Bedroom 36 U/G Present Time Stamp Location Signal 2/1/2018 1:24 Bedroom 40 U/G Call 2/1/2018 1:25 Bedroom 40 U/G Accept 2/1/2018 1:26 Bedroom 40 U/G Present 2/1/2018 1:26 Bedroom 40 U/G Reset Bedroom 46, assistance with no reply. What happens to the poor patient?
Time Stamp Location Signal 2/1/2018 9:58 Bedroom 46 U/G Assistance Thank you for your quick response. This is actual mystry to me as well. The reason it creates multiple Accept is when more than one Health Care Assistant press that they will be Attending that room. System is just dumb and log every single calls. The system don't differenciate anything. Similarly, Display panels are located in each corridor and if any of health Assistant Accept it it will come as multiple Accept. In a way, If we are able to even find Call or Asssitance to Accept or Present then it is good enough and disregard other data.
I can share whole file if you would like to as due to restriction on number of charecter. I can only show very small data.
Please find full set of data in CSV format spit out by system
https://www.dropbox.com/s/lypqc40klj0vb9z/AV-NurseCall%20-%20Original.csv?dl=0
Thank you for your time and effort in advance.
Kind regards.
Based on my understanding, Call or Assistance is the Start of Event, reset or Present or Accepted is the End of the Event,
you need to calculate the time difference between any Start of Event and any End of the Event per room, right?
For example, for room below,
index time room state time difference(seconds) logic row1 2/1/2018 8:42 Bedroom 3 L/G Reset 1 time in row1- time in row3 row2 2/1/2018 8:42 Bedroom 3 L/G Present 1 time in row2- time in row3 row3 2/1/2018 8:41 Bedroom 3 L/G Assistance 0 row4 2/1/2018 8:39 Bedroom 5 L/G Reset 1 time in row4- time in row5 row5 2/1/2018 8:38 Bedroom 5 L/G Call 0 Please let me know if my logic is right so i can test and figure out for you.
Best Regards
Maggie
Good afternoon v-juanli-msft, Thank you for your email and appology for late reply as I was having an issue with logging since Microsoft Community has been moved to Azure AD integration. I must say that support from Micrsoft Community service provider was great where they have logged on to PC today and show me step by step instruction to log me back in.
Yes, your understanding is correct and logic too. However there is also another catch where Health Care Assiatnt press reset button once and it will appear as a present. If they press same button again, then and only then it will turn in to reset status.
So, summarise the case, All the alert starts with Call or Assistance and either end with Present or Reset depnding on how Health Care Assistant interact with it.
There is also another logs which says Emergency (Staff raise emergency to notify other members for help) normally ends with Reset too.
Thank you again for your patience and support.
