1

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.

gus
  • 63
  • 1
  • 7

3 Answers3

1

There was a similar question posed on SO a while ago, take a look: Find number of concurrent users in a SQL records

I hope this works on Mysql too.

Community
  • 1
  • 1
ZippyV
  • 12,540
  • 3
  • 37
  • 52
1

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.

Community
  • 1
  • 1
Lucky
  • 646
  • 4
  • 9
  • To fully meet the original question, you need to add a where clause to the INSERT statement. – Lucky Aug 06 '09 at 04:29
  • Hey, that looks simple enough. Thanks. Is there a way of doing this from a single table, without having to create a new table with the "call_start" data? – gus Aug 06 '09 at 16:00
  • Everything I tried to do it without the temp table either didn't work, or was immensely more complicated. – Lucky Aug 17 '09 at 10:00
  • What about putting the "select distinct ..." in the FROM clause, e.g. "... from calls, (select distinct call_started from calls) starts where ..." – Harold L Aug 19 '09 at 07:38
0

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);
stran
  • 133
  • 4
  • Thanks for the answer, not what I was looking for but I appreciate it nonetheless. – gus Aug 06 '09 at 02:36