iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnEAABJxAfPceo4AAAASdEVYdFNvZnR3YXJlAEdyZWVuc2hvdF5VCAUAAA0cSURBVHhe7Z3fjhXHEYf9Bs4bxG+A38B+A/sN7DdI3sBccYcSKXBhCQkuLPliibGEfcNeJGKvFiFhNgoKijFai+BVEPJCIrAlS+TbU72V2uo/02dn5uyedX1qjU7XVPd0D1O/qZ4zZ3nrTRAEwUoIuQmCYEWE3ARBsCJORm7+8uQ/Nx7tp0qwDjx58uTu3bu7u7upHgTLs2q5efzi5/e/+PatP31DSaZgHQi5CcazUrm59+zVbz79m2jNsnLz40+/nN/eQ6oQrGQKVkjITTCelcrNJ9s/qNYsJTdWp1iIJWuwQkJugvGsh9y8/emOtgq5ORFCboLxrIfc2FYhNydCyE0wnpCboIuQm2A8ITdBFyE3wXhCboIuQm6C8YTcBF2E3ATjGZCbH3/65Y/fPPt48/v3v/jWvjJDeffzh9jZe+/Zq+Q9xMnKDeM8v733+9v/Yi5s+fzloxdpXzBEyE0wngG5+eCr72yo18o71x4QwIMv4C0lN3+49290gYKu2VZoHEphy6AAXX3w3GmlFuz0gKom1xLs/euT/0pJpsXvMES5RLySdQG71L/ds6JNrHbzWftJpgp2hDO9BhlyE4xnQG4+2tx18dkohG5qVmEpubGe7fLen/+Z2mQQh06tigWfhi5YzUUE8URi1CIluS6wdk5gstZBDdX/t9f+nqxH3zb63e0nyZrh5thzxGMQchOMZ0BuuOty0RPPhBxiYQsWGw9S2okGraxzslawnu3SkJtzn/8jd5bi7ERsapNhnZl1nii5AVh5GpRgsIPkFCXrIr9TO6V2blEi67bW2c3+/j6HAD4kU5PXr1+LPyRTnU43RQbDIVJ9wbKd7O3tuU7EwjbVSxR9BluBjNmdPSzWKNUexH9Cxj4qdgoyodzkSlErNkQt7nBUbQrDZxeotX5ybXLFJRQ3Hu3bve3fvqMO1tmJhT0JrFjzFIwTrg6U2hTGM6vcEAk3b968fPnyBQPVzc1NF+0Cxq2trStXriTXQ7Ds7OwkpwzxuXXrVqo3efjwofi7KYuR0aZ6k9u3b4u/7eSzzz7DwjbVSxR9Fj1duH//fqqXkCOyTfUF0lCNUu1BTsLFixcbMscucb5z504y1RkrN+5yn1BulKUOobgwJlNIO45iFYd4Ttaj5HJDWofEMJJ7z16xzVWANZE6t1c3NoVxWRLQv+6luCUVx2XMuhdtSjtmYD65IX7kei3Ctc5Fn1wXcH1jTLsXEJbWgugUwyPtvnBhMEeAS5cuiXNRbmDwVKCJOqoJ5aYd/NPKDc6Dox10sJxZubE6Yh+IOIhYdaMUO3dyQ1TbB7pFXN6U65Fihenqg+fJanAnzY7QHWVwVGOYSW64JS4u7AOIBxtIHIskAvvGxkYyHdUa9lp/UiR6kL3FmJRWMBgbVgFrcjPYiUS+MKHcAHpaTPpADqrKIkgrZ1QYmzgU/3E5q7K3mFXpiWoooOXMyo19rlRLbQSrJowwWQ3WgW57otrlVkUdAZu80HNNlYpLKndaiiOfkDnkhmtULtbGHRu7DS1dQLmUR8FfFAfPZDoEo0pVYyKalUiC4zwXrVM/jXVNo5ORciPd1lZzk8sNSJ+sbZ3G6Rw716dwNuXm2KP64KvvktVg5SZf79SwGlHsFmx60lhzuenIksr2T4rUSKAmYQ65kaCCznuj3ktrkSNo/Dg5ECOxwZbgSdYMiS7cZHhuygddLAbAttGJ5GVs806KUuIo+mABPQlFsZOBufMj/rWTpqer8Y8roul6kGOhOE6GGpxNuXHf6SRrBTuqopocT27s19uUohzYFKw9L7du+vDrx7bac05GMrncaJZeu1HnSGrTc303wlXvycVwlb1yCOmkKDfaSTGGZWo48CHvpDg2R9EHC/BBtAxymRYJcKMS5+JQoUdu1IcZiUX/+WppZpGzKTdkCrZJslaYSW7QF21FyddT9gusxtMlgd7sUx5bJNmZm8nlRu/SndcrES7+PfKkj4RSfYFaGrdliWSJzFwp4KCLRScy/mN0UpQSR9EHC/CBI4ry5gscmZpTlkW7UXIDGxsb+Fy/fl2qPbPI6ZIbrvUvH704v73HTVXeo9XiXqI7JXJjBYLixuyK/XJnQrkBq3ocKFkPsXt7JMOdBykrWEYJk8uNBAZ0rqQ0KmphYymGkFjkc3F1IHdsdkkMS0S5KR90cbQT9+RCU5taJz2BWvTBAvJZH1Fp/AtyVt28Fu3Gyg3zkiPipk002elkQG4ev/j5483v3SXeKKdTbvrLtHLjXsCx79S43Me9blPDLakoK/sPLeaTm1QfQi/xWthY1NkOWCzyWXMTGzByA9dFVq4UcNDFYSd6FNuJtNKXUPJOilLiKPpggVSpPMmSs+pOUe5mKZ6rIpIzMioZXs8/hKMlN+T/tZ8a1coccuMegswqN8XntceWG7ArIPsFmZ3Uue73ZZzy9ryyPBXzyU3nHVKjoucqL4aQWFLlMKR1aSZNSFikCuLgpnzQRdaJphg9nYiFbaqXKPpggVRZII+9QfuXs+pOkfjUzpuMGdxMi+g3g5oDLkVVbrjfOq0hKtALCndUrnst1odqal/ieHLjWrUPIViBYNg61MGS2h9ljNzYwdvfSdg+88c6NZiLtpJC/2nfzMwnN519snwQ/x65kddhwWqZWFLFhJms5mq64Ia3aPH/TnRU4iZNND+CWrdsU71E0QcLpMoh7vH53HKjJ3apJ8RKVW7sD3/aL5uoG6UWscKJyM34FGCM3LgXcGTR5IydD1/cedAy69t9yuRyoxduj3wI8qwkf6EmR277NsuAxdGOhKusnohqWZi48JaYd1M+6OJoJ/JgGGeJ28FO9KCpXkJaOR8skCqHoKfySEVOy9xys5RzTlVu7AXdfk3Oep5CuaEk63EZIzdgm8uZtN/TN163sdgXAl1p/Lh0QiaXG5A4aby94pDAHrzWuc9Lz7pQEqRtqiwgVsUo/u6hda4UsHD3nehE2Dr/vBNRBJqkegkR1qJqpIpBhRuRXUu5IaTtBd2O8H7PlcmNazLyu5uRcmMf08jPsuyaqPNZr21CsuleLGK+yW825pAbiQ2oRYJD1YE7eePBgaqSXUmBGFPlEH384bQJOuUGdCIuH4G8E11/2TWXRUPayZ8YU+UoOgtZW6233CRrBeu5lNwcbxHRIzdu/P0PR4qMlBumaV/ns+oz+LqN4MRFUiQ7KsrcS6o55EbTEKj9nphAskKggU1cOTUBOrx+/bo45B2KPVUOkTFA3lu/3CzbiRjxz88nEoOdvblyHRy4IjcgfQrrLTftCO/3PIZwgAu2nnTAfc2cv/OyFCPlBtxrh1p6Xrdxz+x1ANitis29pJpDbkCjC1iMoBEcQuCzLE9cGGvyAojL1tYWqwm22LWrPFUB2ZUqBsQCUsXQLzewVCdMR4fKFHZ2dhYz3t3c3BQje11qA7IrVTI4uva5ZnLjnhQ0sgP31LOtIPbGTul8bOG0r7OVi/AxCc54uXFT0CJPjtvYo6Mvtkkx65mJmeQGiCtZAhQhXPPAYxkiTzdysNcWKeKQKh0sJTc1ip1AY9ZMIZ8yyN5UKaFasGZyA/bO+c61B3lgIEn5G4DcY+XvB5NQ5BGeP+/EU3omH5EXl/NFgVM0iv5dZLb9rXArLt8ahxbGyw3kP0E41/G6jROU/BmNHRtJkJyWOZhPbgQyFFISgpNbNPHGB5ZR7cPRhBDa2NjAmS2fsaR9JXCDVOmAAeDvIn+SThSUkZGL7jBrPte0EnoOzRnIO5GGtZ4ZmzjUBmlZyjmnKjf5C6woiPwHBh9+/di++F8rxW+gaz/80TL4m+xiKS4l3NpNCp4yEflBhp1IbT0yidzkgxlMuNAOu4wqypNT1ZHLxgZzy03wa6AqN9zzB6WBgk9DC/J8obas0FIUqTwtyktyPUrtoUmtFNOfSeQmz7aKx7K4E1tLvpyQzbSkCrkJxlOVG+D6zl9jtYUMiJghkOzKS4t70KBwVy/6S6n9aZgbj/YbrRoLkzxNa5TigCeRG7D9DD6Bcs+50JS0o4T9Z5ppSRVyE4ynJTcC1z0SoJkOMU/YcAu1N2c+Ez/qw9VPkDcuevzpgW7pSjqkEFG1G7hQbMWBBr+rYiQMr6FWjLwxYAYmbdkOroAaMHI94uCYmalMUOaYrBU4b+pMaZ/G4xFyE4xnWG7OEsQhcY58SCGkWdzNkQvkcBTVGmQrWdeHkJtgPL8uuTlB7JpuMFs5hYTcBOMJuVkFLAPtd0yryaemJeQmGE/IzSpg4aZa896Ih80nSMhNMJ6Qm9m59+yVTW3aL16fWkJugvGE3MwIa6jz23tWa9Y0tYGQm2A8ITcTg6AgK+8e/YvxUt6u/8d1p5+Qm2A8ITcT4yRGC1ozx+swKyPkJhhPyM3EOJWR8tHm7vrmNULITTCekJuJ0fee2X6y/cPVB8/X8WvvnJcvXz59+jT/81FB0E/ITRAEKyLkJgiCFRFyEwTBSnjz5n+Bsg0wC+hiuAAAAABJRU5ErkJggg==
Weekly Challenge #106
This week, we have a simple data set that represents times inhabitants enter and exits rooms within their household. Assuming inhabitants have the lights of the room on whenever they are present and off when they are not (think motion detection), how many minutes were lights on in each of the rooms?
Note: If there are overlapping time entries, assume the light is on as long as someone is present in the room.
Example: Person 1 is in the room from 12:30 PM - 1:30 PM, and person 2 is in the same room from 1:00 PM to 2:00PM. The amount of time the light was on was from 12:30 PM to 2:00PM (do not count duplicate time).
Output
Input
C:\Users\hmyers\AppData\Local\Temp\Engine_2876_07207ae04cf74d82af2cdc619b8db31b_\Engine_9288_774fdbb8136e48c2802a8acd556d4e4d_.yxdb
Single
Profile
Final output
C:\Users\hmyers\AppData\Local\Temp\Engine_2876_07207ae04cf74d82af2cdc619b8db31b_\Engine_9288_456c8f7475084c73a800fb89c2b3d88c_.yxdb
Single
Profile
Living Room
9:52 AM
9:55 AM
Living Room
10:15 AM
10:45 AM
Living Room
10:30 AM
10:50 AM
Living Room
12:36 PM
12:38 PM
Living Room
12:36 PM
1:15 PM
Living Room
12:50 PM
1:16 PM
Living Room
2:05 PM
2:20 PM
Living Room
3:14 PM
4:10 PM
Living Room
3:20 PM
3:35 PM
Living Room
5:01 PM
5:25 PM
Living Room
7:36 PM
9:45 PM
Kitchen
7:15 AM
7:20 AM
Kitchen
7:23 AM
8:20 AM
Kitchen
7:27 AM
8:10 AM
Kitchen
1:16 PM
1:45 PM
Kitchen
1:20 PM
2:50 PM
Kitchen
5:30 PM
6:20 PM
Kitchen
5:30 PM
6:20 PM
Bathroom
12:36 AM
12:40 AM
Bathroom
4:30 AM
4:33 AM
Bathroom
6:30 AM
6:51 AM
Bathroom
6:33 AM
6:45 AM
Bathroom
10:55 AM
11:00 AM
Bathroom
12:00 PM
12:05 PM
Bathroom
3:01 PM
3:07 PM
Bathroom
6:21 PM
6:25 PM
Bathroom
9:50 PM
10:10 PM
Bathroom
9:55 PM
10:09 PM
Data input
Bathroom
68
Kitchen
206
Living Room
302
Enter
%I:mm %p
Enter Time
Convert Enter from string to time
Convert Enter From:
Custom
Exit
%I:mm %p
Exit Time
Convert Exit from string to time
Convert Exit From:
Custom
Calculate minutes lights are on
Keep only necessary columns
RecordID
1
Int32
6
0
Add Record ID
RecordID
Overlaps
Int32
4
Empty
IF (DateTimeDiff([Row+1:Enter Time],[Enter Time],"minutes") > 0 &&
DateTimeDiff([Row+1:Enter Time],[Exit Time],"minutes") < 0) ||
(DateTimeDiff([Row+1:Exit Time],[Enter Time],"minutes") > 0 &&
DateTimeDiff([Row+1:Exit Time],[Exit Time],"minutes") < 0) ||
(DateTimeDiff([Row+1:Enter Time],[Enter Time],"minutes") = 0 &&
DateTimeDiff([Row+1:Exit Time],[Exit Time],"minutes") = 0) THEN 1 ELSE 0 ENDIF
Find overlapping times
IF (DateTimeDiff([Row+1:Enter Time],[Enter Time],"minutes") >...
Exit Time
NewField
Int32
4
Empty
IF [Row+1:Delete] = 2
THEN [Row+1:Exit Time]
ELSE [Exit Time]
ENDIF
Update Exit Time from overlapping rows below
IF [Row+1:Delete] = 2
THEN [Row+1:Exit Time]
ELSE [Exit Tim...
Simple
Delete
=
0
Keep only rows where Delete = 0
[Delete] = 0
Sum minutes lights are on, grouped by room
RecordID
Overlaps
Int32
4
Empty
IF (DateTimeDiff([Row-1:Enter Time],[Enter Time],"minutes") > 0 &&
DateTimeDiff([Row-1:Enter Time],[Exit Time],"minutes") < 0) ||
(DateTimeDiff([Row-1:Exit Time],[Enter Time],"minutes") > 0 &&
DateTimeDiff([Row-1:Exit Time],[Exit Time],"minutes") < 0) THEN 1 ELSE 0 ENDIF
Second pass in finding overlapping times
IF (DateTimeDiff([Row-1:Enter Time],[Enter Time],"minutes") >...
Keep only necessary columns
Exit Time
NewField
Int32
4
Empty
IF [Row+1:Overlaps] = 1
THEN [Row+1:Exit Time]
ELSE [Exit Time]
ENDIF
Update Exit Time from overlapping rows below
IF [Row+1:Overlaps] = 1
THEN [Row+1:Exit Time]
ELSE [Exit T...
Simple
Overlaps
=
0
Keep only rows where Delete = 0
[Overlaps] = 0
Overlaps
Delete
Int32
4
Empty
IF [Row-1:Overlaps] = 1 AND [Row-1:Exit Time] > [Enter Time] AND [Row-1:Exit Time] > [Exit Time]
THEN 1
ELSEIF [Row-1:Overlaps] = 1
THEN 2
ELSE 0
ENDIF
Determine if the entire row overlaps and can be deleted
IF [Row-1:Overlaps] = 1 AND [Row-1:Exit Time] > [Enter Time] ...
Horizontal
challenge_106_start_file FINISHED