Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandippanchal
New Member

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 5
v-juanli-msft
Community Support
Community Support

Hi @sandippanchal

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.

 

sandippanchal
New Member

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.

 

LivioLanzo
Solution Sage
Solution Sage

Hi @sandippanchal

 

I have some question regarding your data set:

 

Bedroom 15, how can you have one Reset without any call / assistance ?

 

Time StampLocationSignal
2/1/2018 9:04Bedroom 15 L/GReset

 

Bedroom 28, same scenario:

 

Time StampLocationSignal
2/1/2018 7:27Bedroom 28 L/GAccept
2/1/2018 7:28Bedroom 28 L/G

Present

 

 

 

Bedroom 43, same scenario:

 

Time StampLocationSignal
2/1/2018 0:53Bedroom 43 U/GPresent

 

 

Bedroom 3, you have one call / assistance, followed by multiple accept present, which of the two is relevant and why, Reset or Present?

 

Time StampLocationSignal
2/1/2018 8:41Bedroom 3 L/GAssistance
2/1/2018 8:42Bedroom 3 L/GReset
2/1/2018 8:42Bedroom 3 L/GPresent

 

same goes for bedroom 36:

 

Time StampLocationSignal
1/1/2018 23:48Bedroom 36 U/GAssistance
1/1/2018 23:48Bedroom 36 U/GAccept
1/1/2018 23:49Bedroom 36 U/GReset
1/1/2018 23:49Bedroom 36 U/GPresent
2/1/2018 0:09Bedroom 36 U/GCall
2/1/2018 0:09Bedroom 36 U/GAccept
2/1/2018 0:10Bedroom 36 U/GPresent
2/1/2018 3:40Bedroom 36 U/GAssistance
2/1/2018 3:40Bedroom 36 U/GAccept
2/1/2018 3:41Bedroom 36 U/GPresent

 

Time StampLocationSignal
2/1/2018 1:24Bedroom 40 U/GCall
2/1/2018 1:25Bedroom 40 U/GAccept
2/1/2018 1:26Bedroom 40 U/GPresent
2/1/2018 1:26Bedroom 40 U/GReset

 

Bedroom 46, assistance with no reply. What happens to the poor patient?

 

Time StampLocationSignal
2/1/2018 9:58Bedroom 46 U/GAssistance

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.