Challenge #106: How Long were the Lights On?

[TonyA]

I still haven't gotten the hang of these brute force row generation solutions. Haven't seen anyone do it quite the way I did, but I didn't check all 11 pages of solutions.

Spoiler

I scanned through the table and if the start time on an interval was less than the end time on the previous interval, then I set the start time to that end time to eliminate overlaps. Then did the difference and sum. But that is a lot more calculation than simple counting so I can see where the row generation solution could be faster.

[nick_ceneviva]

Solution attached

Spoiler

[Verakso]

It was a dark and stormy nightWhen I tried to solve this, and it was getting to late.

So the logic to handle the overlap of the lights and people entering the room, was to tricky at that point.

But after a good nights sleep, I did nailed it, so here is my solution

Spoiler

First I used DateTime parse tool, to convert the Enter and Exit times to a real time object.

Then I sorted the new Enter and Exit times to make sure that they where in a correct order for the Multi-Row Formula tool.

That I used to group the Enter and Exit times so I could keep track of the overlapping persons entering at room, where the light was allready on (or nor leaving as the last person).

This was accomplished by grouping on the Room, and the newly created Group from the Multi-Row Formula tool, and the find the Min Enter time and the Max Enter time.

And finally calculating the minutes between the min and max times, and summarize it at the end.

I think I am getting better at this, since I did manage to solve this without peeking. I was close of doing that sometimes, but I kept myself for doing it, and in the end. I did manage to do it on my own,

Still Climbing

Thomas

[nikolaskardasis]

Spoiler

[cgoodman3]

Spoiler

Need to get better at the in-built DateTime parsing options to use fewer tools at the front end.

[hbraunius]

My solution attached.

[vfranklin]

Solution

[MsBindy]

Not particularly elegant,  but I got the right answer.

[BradWerner]

Was thinking along the lines of what @patrick_digan did. Thanks to his solution for getting me there.

Spoiler

[DavidThorpe]

My solution: