We start by figuring out which hour of the day we are working with and store this value in a variable called __Hour. Next, we determine the hour and minute of our start time, __StartHour and __StartMinutes, as well as the hour and minute of our end time, __EndHour and __EndMinutes.
Next, we use GENERATESERIES to create a table of hours inclusive of our starting hour and ending hour and store this in a variable called __Table. We then add a column to this table using ADDCOLUMNS that determines how many minutes within each hour the event consumed. This is done by a complex SWITCH statement.
The SWITCH statement has three conditions as well as a default value. The first condition is if the start hour is less than the end hour and the hour in the table is neither the start hour or the end hour, then we know that this hour was completely consumed by the event and hence we assign a value for the minutes of 60, the full hour. The second condition is the case where the start hour is less than the end hour and the value we are working with in our table is equal to the end hour of the event. In this case, we can simply assign the __EndMinutes computed earlier. The third condition is similar to the second except that for this condition to be true, we are working with our start hour. In this case, we assign 60 minutes minus our __StartMinutes. If none of these conditions are true, then we know that the start and end hours are the same and hence we simply subtract __EndMinutes from __StartMinutes.
We filter our table to remove any rows with no minutes and for our final RETURN statement, we essentially look up our current working hour, __Hour, within the table and return the value of the __minutes column computed by our SWITCH statement.