Challenge #106: How Long were the Lights On?

[JeremyGonzva]

Solution

[Ben_Jeffreys]

My submission

[grazitti_sapna]

Hi, Please find my solution for the given problem.

[quandai]

Attached is my solution.

[edavilac]

Thanks for the challenge, sharing my solution. 

Spoiler

I need to refine my time conversion technique, I used a lot of "Multi-field Formula" steps, then saw in previous posts that is was possible to make the whole time conversion within the "Generate Rows" tool. Learned a lot!

[izamryan]

This was one of the best ones I've done so far! Great little puzzle, thank you for sharing this one. What can I say!?

I ❤️ the thrill of solving! Hope you guys are having fun too.

I think we should come up with a Daft Punk remix of ... "Clean it ... Munge it! Grep it ... SELECT it! CLEANER, MUNGER, GREPER, SELECTER!!!"

Spoiler

3 main steps here: Clean, Analyse, Solve

1. Clean it!
The raw time stamp data needs to be put into date/time format so we can use the built-ins datetimediff().
It helps to think about what your "unit of measure" is when solving these challenges: here the "unit of measure" is "room-minutes" which I've called "duration" throughout.


2. Analyse it!
We can then calculate, for each row, the Duration which = datetimdiff(Exit_dt,Enter_dt,"minutes"). This calculation isn't strictly necessary but when analysing it, I preferred to have this so I can double-check my logic as I was walking through the workflow.

We then give a Unique ID to each row to facilitate the next calculation which is essentially a pivot.

Three multi-row formulas, which:
a. Calculate the overlap (in minutes). Actually you can combine this with b. below but I prefer to do this serially step by step to make the workflow more readable and understandable. You can also benefit by having the signs (+ve and -ve, which can signpost if your next step is correct or not.
b. Identify which room sessions have an overlap.
c. For each of the identified overlap sessions : group all all the overlap sessions together and give them an index = the uniqueID of the first session in that overlap grouping.

3. Now Solve!
Summarise tool, grouping by the new Index we just created. This collapses the overlap groups together and gives the minimum time that the lights were first turned on and the maximum time that the lights were turned off.

You can't use the duration in minutes here because that won't take care of the edge cases of the first user coming in and going before the second user has used the room for longer.

Once you've got min/max of the room-sessions, you can now solve for the room-minutes with a Summarise tool.

voila! Same result as the given Output node.

[Emil_Kos]

Hi,

Sharing my solution.
I think this exercise is very useful as I believe similar user cases are quite common.

Spoiler

[Ladarthure]

Fun one!

[balajilolla]

Spoiler

Solution Attached

[deviseetharaman]

Spoiler