Finding concurrent calls per day from the Asterisk cdr database table.

Create the following procedure in MySQL where the cdr table is present. 
execute the procedure using the following command;

mysql> tee output.txt
To save the output into a test file
mysql> call concurrent;
This procedure will display the start date and end date for which the data is computed. 
it also displays the day and the maximum concurrent calls for that day.
mysql> tee off; 
To stop writing to the file.

Slightly slower version

delimiter $$
drop procedure if exists concurrent$$

create procedure `concurrent`()
BEGIN
    SET @start_date_global = date((select min(calldate) from cdr));
    SET @end_date_global = date((select max(calldate) from cdr ));
    select @start_date_global,@end_date_global;
    WHILE (@start_date_global <= @end_date_global) DO
       
        create temporary table if not exists one_day (index(calldate)) ENGINE=MEMORY AS (select * from cdr where date(calldate) = @start_date_global);
        SET @start_date = (select min(calldate) from one_day);
        SET @end_date = (select max(calldate) from one_day);
        SET @max_conc = 0;
        SET @max_conc_val =0;
--        select @start_date,@end_date;
         WHILE (@start_date <= @end_date) DO
            SET @max_conc_val = (select count(a.uniqueid) from one_day a where date(a.calldate) = date(@start_date) and @start_date between a.calldate and date_add(a.calldate,INTERVAL a.duration second) );
            IF (@max_conc_val > @max_conc) THEN
                SET @max_conc = @max_conc_val;
            END IF;
            SET @start_date = date_add(@start_date, INTERVAL 1 second);
--        select @start_date,@max_conc_val,@max_conc;
        END WHILE;
        select @start_date_global,@max_conc;
        SET @start_date_global = date_add(@start_date_global, INTERVAL 1 day);
        drop temporary table one_day;
    END WHILE;
END $$

delimiter ;

Faster Version

delimiter $$
drop procedure if exists fast_concurrent$$

create procedure `fast_concurrent`()
BEGIN
    SET @start_date_global = date((select min(calldate) from cdr));
    SET @end_date_global = date((select max(calldate) from cdr ));
    select @start_date_global,@end_date_global;
    create temporary table full_time(time_of_day time PRIMARY KEY, ct int) ENGINE=MEMORY;
    SET @start_of_day = '1990-01-01 00:00:00';
    SET @end_of_day = '1990-01-01 23:59:59';
    WHILE (@start_of_day <= @end_of_day) DO
        INSERT INTO full_time values (time(@start_of_day),0);
        SET @start_of_day = date_add(@start_of_day, INTERVAL 1 second);
    END WHILE;
--    select 'full time table built' as result1;   
    WHILE (@start_date_global <= @end_date_global) DO
        create temporary table if not exists one_day (index(calldate)) ENGINE=MEMORY AS (select * from cdr where date(calldate) = @start_date_global);
        SET @row_ct = (select count(*) from one_day);
        SET @row_iter = 1;
        BLOCK2: BEGIN
            DECLARE start_date DATETIME;
            DECLARE end_date DATETIME;
            DECLARE cur_row_iter cursor for select calldate,date_add(calldate,INTERVAL duration second) from one_day;
            open cur_row_iter;
            WHILE (@row_iter <= @row_ct) DO
                FETCH cur_row_iter into start_date,end_date;
                update full_time set ct=ct+1 where time_of_day between time(start_date) and time(end_date);
                SET @row_iter=@row_iter +1;
--                select 'updating row', @row_iter,@row_ct;
            END WHILE;
        END BLOCK2;
        SET @max_conc = (select max(ct) from full_time);
        select @start_date_global,@max_conc;
        update full_time set ct=0;
        SET @start_date_global = date_add(@start_date_global, INTERVAL 1 day);
        drop temporary table one_day;
    END WHILE;
    drop temporary table full_time;
END $$

delimiter ;

Example output

mysql> call concurrent();
+--------------------+------------------+
| @start_date_global | @end_date_global |
+--------------------+------------------+
| 2015-07-01         | 2015-08-13       |
+--------------------+------------------+
1 row in set (1.55 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-01         |        39 |
+--------------------+-----------+
1 row in set (5 min 56.72 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-02         |        37 |
+--------------------+-----------+
1 row in set (11 min 36.81 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-03         |         7 |
+--------------------+-----------+
1 row in set (12 min 11.05 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-04         |         3 |
+--------------------+-----------+
1 row in set (12 min 20.40 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-05         |         2 |
+--------------------+-----------+
1 row in set (12 min 29.21 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-06         |        27 |
+--------------------+-----------+
1 row in set (17 min 59.58 sec)

+--------------------+-----------+
| @start_date_global | @max_conc |
+--------------------+-----------+
| 2015-07-07         |        57 |
+--------------------+-----------+

1 row in set (25 min 43.06 sec)

 As you can see depending on the data the calculation is going to take a long time. If anyone improves on this please comment so that every one can benefit. Also depending on need the output can be written into another table so that you can use it for future and not have to calculate every time. 
Another use case is run this for just one day every day and write the output to a table so when you need it you can just look at the stored table. 
If you want to just compute this for a filtered data just add the filter in the temporary table creation script and that should work.