Many to many relationship users to times logged in/out - how to make scalable calculation of total avail time?
<p>I've been trying to do the following:</p>

<p>I have a list of data - can be anything from 10k-200k entries.</p>

<p>The schema is along the lines of:</p>

<pre><code>Connection Status | Timestamp | Username
</code></pre>

<p>Sample data has been included below.</p>

<p>We could have multiple entries per username, and a user can log in or out as many times in a day as required (reachable, unreachable).</p>

<p>The data will always be for a particular day (<code>0000 to 2359</code> each day)</p>

<p>I'm trying to figure out the percentage of logged in time against total session time.</p>

<p>I can easily find the total session time by creating a pivot, and working out the max-min to get the total session time.</p>

<p>What I'm struggling with is creating a formula to calculate the actual logged in time.</p>

<p>So for example:</p>

<pre><code>USERNAME | STATUS   | TIME STAMP
User1    | logs in  | 08:00
User1    | logs out | 08:30
User1    | logs in  | 10:00
User1    | logs out | 12:00
User1    | logs in  | 15:00
User1    | logs out | 16:00
</code></pre>

<p>Total time here would be 8 hours.
Actual in use time would be:</p>

<pre><code>0830-0800 = 0.5 hours
+
1200-1000 = 2 hours
+
1600-1500 = 1 hour
= 3.5 hours total.
</code></pre>

<p>This would mean a calculation that will check the first user it starts with (User1), checks if it's User1 we're dealing with (if not, will reset total time to 0 for the next user or something along those lines).</p>

<p>This then needs to check if the status associated with the time is "Reachable" or "Unreachable".</p>

<p>We can have multiple reachable/unreachable such that we don't always have reachable, unreachable. (We could have multiple instances that are reported as reachable before the next unreachable) but in this case, we're only interested in the first instance of the new status and ignore the rest until a new status is found.</p>

<p>For example:</p>

<pre><code>Reachable   |01/06/2016 06:25   |user2
Reachable   |01/06/2016 06:25   |user2
Reachable   |01/06/2016 06:59   |user2
Reachable   |01/06/2016 08:00   |user2
Unreachable |01/06/2016 09:57   |user2
</code></pre>

<p>I'm only interested in the first instance, as it continues to be reachable until 0957.</p>

<p>Is there a way to do this, or am I going about this in completely the wrong fashion? I could also perhaps write an application, or use Access to handle some of this (??)</p>

<p>Sorry if this question is unclear, it's the best that I can articulate it. If you need clarification, I'll try my best.</p>

<p>Thanks all, </p>

<p>Edit: AP Forward Ed, I'll include a sample of some data... I hope this is how I do it.</p>

<pre><code>Status      |Time               |User Name
Reachable   |01/06/2016 06:57   |user1
Unreachable |01/06/2016 07:16   |user1
Reachable   |01/06/2016 07:16   |user1
Unreachable |01/06/2016 09:14   |user1
Reachable   |01/06/2016 09:25   |user1
Reachable   |01/06/2016 09:25   |user1
Unreachable |01/06/2016 10:50   |user1
Reachable   |01/06/2016 12:33   |user1
Reachable   |01/06/2016 12:33   |user1
Unreachable |01/06/2016 13:13   |user1
Reachable   |01/06/2016 14:15   |user1
Reachable   |01/06/2016 14:15   |user1
Reachable   |01/06/2016 06:25   |user2
Reachable   |01/06/2016 06:25   |user2
Reachable   |01/06/2016 06:59   |user2
Reachable   |01/06/2016 08:00   |user2
Unreachable |01/06/2016 09:57   |user2
Reachable   |01/06/2016 10:00   |user2
Reachable   |01/06/2016 10:00   |user2
Unreachable |01/06/2016 10:27   |user2
Reachable   |01/06/2016 10:54   |user2
Reachable   |01/06/2016 10:54   |user2
Unreachable |01/06/2016 11:24   |user2
Reachable   |01/06/2016 11:24   |user2
Reachable   |01/06/2016 11:24   |user2
Unreachable |01/06/2016 11:33   |user2
Reachable   |01/06/2016 11:33   |user2
Reachable   |01/06/2016 11:33   |user2
Unreachable |01/06/2016 11:59   |user2
Reachable   |01/06/2016 12:05   |user2
Reachable   |01/06/2016 12:05   |user2
Unreachable |01/06/2016 12:11   |user2
Reachable   |01/06/2016 12:15   |user2
Reachable   |01/06/2016 12:15   |user2
Unreachable |01/06/2016 12:15   |user2
Reachable   |01/06/2016 12:15   |user2
Unreachable |01/06/2016 12:16   |user2
Unreachable |01/06/2016 12:17   |user2
Reachable   |01/06/2016 12:17   |user2
Unreachable |01/06/2016 12:18   |user2
Reachable   |01/06/2016 12:20   |user2
Reachable   |01/06/2016 12:20   |user2
Unreachable |01/06/2016 12:21   |user2
Reachable   |01/06/2016 12:21   |user2
Unreachable |01/06/2016 12:21   |user2
Reachable   |01/06/2016 12:21   |user2
Unreachable |01/06/2016 12:21   |user2
Reachable   |01/06/2016 12:22   |user2
Unreachable |01/06/2016 12:22   |user2
Reachable   |01/06/2016 12:22   |user2
Reachable   |01/06/2016 12:22   |user2
Unreachable |01/06/2016 12:23   |user2
Reachable   |01/06/2016 12:30   |user2
Reachable   |01/06/2016 12:30   |user2
Unreachable |01/06/2016 12:36   |user2
Reachable   |01/06/2016 12:36   |user2
Unreachable |01/06/2016 12:36   |user2
Reachable   |01/06/2016 12:36   |user2
Unreachable |01/06/2016 12:37   |user2
Reachable   |01/06/2016 12:37   |user2
Reachable   |01/06/2016 12:37   |user2
Reachable   |01/06/2016 12:37   |user2
Unreachable |01/06/2016 12:41   |user2
Reachable   |01/06/2016 12:58   |user2
Reachable   |01/06/2016 12:58   |user2
Unreachable |01/06/2016 13:00   |user2
Reachable   |01/06/2016 13:02   |user2
Reachable   |01/06/2016 13:16   |user2
Unreachable |01/06/2016 13:36   |user2
Reachable   |01/06/2016 13:36   |user2
Reachable   |01/06/2016 13:36   |user2
Unreachable |01/06/2016 13:48   |user2
Reachable   |01/06/2016 14:04   |user2
Reachable   |01/06/2016 14:04   |user2
Reachable   |01/06/2016 07:00   |user3
Reachable   |01/06/2016 12:44   |user3
Reachable   |01/06/2016 12:44   |user3
Reachable   |01/06/2016 12:52   |user3
Unreachable |01/06/2016 14:19   |user3
Reachable   |01/06/2016 14:19   |user3
</code></pre>

<p>We call it Reachable/Unreachable but yeah - same thing!</p>

<p>This is a small sample, but yes we have 2,3, maybe 4k users to trawl through sometimes.</p>