Search

Data mining Shoretel

I have a data mining project that has me stumped.

Attached is a list of dates, times and durations of work that comes into a group of total positions per date/time duration.  Each position is defined by the column POS in the following sample:

Date                     Time              POS      Duration
12/23/2010      17:24:11      392      0:00:39
12/23/2010      17:24:50      235      0:01:20
12/23/2010      17:32:56      358      0:00:00
12/23/2010      17:34:06      368      0:01:28
12/23/2010      17:51:24      235      0:02:41
12/23/2010      18:22:14      235      0:00:58
12/23/2010      18:29:35      392      0:02:58
12/23/2010      18:33:54      235      0:01:20
12/23/2010      18:38:56      358      0:00:00
12/23/2010      18:41:23      235      0:01:01
12/23/2010      18:43:17      235      0:00:00
12/23/2010      18:43:57      235      0:00:00
12/23/2010      18:44:02      235      0:01:36
12/23/2010      18:56:53      358      0:00:00
12/23/2010      19:10:37      283      0:00:00
12/23/2010      19:16:08      212      0:00:39
12/23/2010      19:16:14      212      0:01:02
12/23/2010      19:16:43      248      0:00:23
12/23/2010      19:17:05      399      0:00:26
12/23/2010      19:16:24      393      0:01:17
12/23/2010      19:17:35      368      0:00:12
12/23/2010      19:17:49      368      0:00:03
12/23/2010      19:16:34      395      0:01:28
12/23/2010      19:16:25      394      0:01:52

What I’m trying to get is the total amount of time that all of the positions (normally, there are 8 positions per sample spectrum interval) are occupied with work.  For each condition where all positions are busy, I need to know the cumulative count, time and duration of each.  I also want to know the number of concurrent positions being used at the start time of each call.
To state (somewhat) graphically in the attached ASCII art snippet, if each position was graphed on a Gantt chart and stacked, I want to know a count of when all eight bars are occupied and for how long.

I’m trying to do this in Excel…





















Example Gantt Chart of work time per position

POS |  Work Time in seconds -->
1   |-------    ------   --   ------- ----
2   |---  -- -----    ----   -------------------
3   |  ------------------------  ---------------
4   |-------------------------------------------
5   |-----------       -----  ------------------
6   |  ----------------------- ---------------- 
7   | ----------    ------------- --------------
8   |------------- -------  ----------- ------ -
       ^  ^^             ^        ^^^ ^ ^^^
       |  ||             |        ||| | |||
^
| = busy peg
Total: 11 cumulative busy pegs
11 seconds of busy time:
00:00:03
00:00:06
00:00:07
[...]
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s