Analyzing Phone Call Records in MySQL DB

Question

Hello everyone,

Last time I asked a question here, I got tons of great ideas on how to solve a particular problem. Like I explained last time, programming is just something I love to do, but it's not something I studied in school, so I have no proper methods of doing things. I'm an Electrical Engineer struggling through a recession doing some programming gigs here and there.

Currently I'm working for a company doing VoIP. The software they bought lacks in many important features other system have, so I decided to start building a few tools that will allow me to do these missing things.

One of the things I consider to be very important is finding out the MAX LOAD of CALLS in a particular given time (maximum number of simultaneous calls). For example, I want to know the maximum simultaneous phone calls on August 3rd.

Now, luckyly for me, this software stores all it's information on a MySQL database, so I have a table with ALL the calls since the system first started running.

The "Calls" table has 2 files called "call_start" and "call end", and they are type "datetime".

Based on this information, I would like to figure out how I could query the database and find out what was the highest number of active calls simultaneously in a time frame specified by me.

I'm working with C# mostly, so ideas using C# would be welcomed. I'm not sure if it could be done exclusively in MySQL, but that would also work.

Thanks everyone for the help.

Answer 1

There was a similar question posed on SO a while ago, take a look: http://stackoverflow.com/questions/1117004/find-number-of-concurrent-users-in-a-sql-records/1117165#1117165

I hope this works on Mysql too.

Answer 2

Not the most efficient way to do it, but this will give you a count per hour (for today), including all calls that start and end within that hour.

select hour(call_start),count(*) from calls 
where call_end <= hour(call_start) and date(call_start) = date(now())
group by hour(call_start);

Answer 3

I looked at the similar question and didn't see a simple answer. Basically, you want to know how many calls there are every time a call starts. The following would work

create temporary table starts (call_started time);
insert into starts select distinct call_started from calls;

select starts.call_started, count(*)
  from starts, calls
  where starts.call_started >= calls.call_started and starts.call_started <= calls.call_ended
  group by starts.call_started
  order by count(*) desc;

If you only wanted the max load, just add "LIMIT 1" to the final query. If you want to graph the results, just sort by start time instead.