Database Analysis: Line Utilization

Discussion in 'OT Technology' started by 5Gen_Prelude, Mar 10, 2003.

  1. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Alright, here's the deal. I have to do a line utilization analysis. The database tells me when the call is generated, how long the call was, and which line was used. What I can't figure out is how to calculate what the minimum number of lines were required to make the same calls.

    So sample data would be:

    5551212 1/1/3 12:00:00am 146
    5551213 1/1/3 12:02:00am 30
    5551213 1/1/3 12:04:00am 30
    5551214 1/1/3 12:03:00am 46
    5551214 1/1/3 12:07:00am 46

    In this case only 2 lines are being used at one time. Now how do I do this for way more records?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    i'm not sure i understand what "calculate what the min number of lines were required to make the same call" means. You only have one record per call right? Not an initial dial out, and then hang up right?
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Each line is a call record, the first field indicating the phone number, the second the start time, the third the duraction. In this case 3 lines were used to make those calls, however, I only needed two lines to make those same 5 calls (since the first call overlaps 2 other calls). Easy enough when there's 3 lines and only 5 call records - now how do you do it when there's thousands of call records and more lines? Get a computer to do it - but that's what I'm stuck on - the logic on how to do it.
     
  4. Bono

    Bono Guest

    This is how I'd do it:

    Place the first call somewhere named "needed_line_1".

    Then, for any given call, check if the existing lines (needed_line_x) are busy. If not, place it into a free line. If all lines are busy, create a new line and place the call into it.

    To know if a given line is busy, you have to make two timestamps for each of the calls it contains: start and stop. Then create a start timestamp for the new call you are analysing and compare it to the previous timestamps. Line is busy if (start.new_call > start.line_call AND start.new_call < stop.line_call).

    This is far from being efficient, but it should be working and easy to do.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That's definately an idea. My brother suggested doing a time line and filling in the number of calls used per second over a given time frame. If the call was using that time, increase the counter for each time slice that is applicable by 1. This method would allow you not only to see the maximum, but also trends as well (by graphing the time line with the contents in each time slice).

    Good suggestions - any others? I was thinking there was a whiz-bang SQL statement I could use but I'm thinking not so much.
     
  6. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    I don't understand how you only get two lines..

    Since that first call overlaps all of them?

    I can probably help with an SQL statement if I can understand that first issue.
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    The first call overlaps the call starting at 12:02:00 but that's it (146 seconds which is 2:26, or an end time of 12:02:26. Too late for the 12:02:00 call to use the same line)
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Alrighty, this takes a long time but I think it works:

    Code:
    UPDATE CallRecordsTemp, TimeLine SET TimeLine.Count = IIf([timelineid]>=DateDiff("n","1/1/3",[start]) And [timelineid]<=DateDiff("n","1/1/3",[end]),[count]+1,[count]);
    TimelineID = the minute of the month, count is the number of calls being used at one time. The tables aren't joined causing it to match every record in one table with every record in the other.

    I should convert the start and end dates to minutes to decrease the processing time but that's the gist of it. I just can't see this being very efficient with 15000 records ;)
     

Share This Page