Re: [1'b68'1 astrad v8] clear stat of telephones with problems on all servers

Code MySQL de la Procedure utilisée ci-dessous:

delimiter |
DROP PROCEDURE IF EXISTS `StatReg2`|
CREATE PROCEDURE StatReg2( IN DOM VARCHAR(15) , IN ST datetime, IN SP datetime, IN ITV INT(5))
BEGIN
  DECLARE TMP1 datetime default '';
  DECLARE TMP2 datetime default '';
  DECLARE NB INT(10) default 0;
  DECLARE done INT DEFAULT 0;
  DECLARE USR VARCHAR(11);
  DECLARE cur CURSOR FOR select distinct(username) from location2_history where domain=DOM;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;    
  DROP TABLE IF EXISTS timetempo;
    CREATE TEMPORARY TABLE timetempo (
    `START` VARCHAR(19) default '',
	`STOP` VARCHAR(19) default '',
	`COUNT = 1` INT(10) default 0,
	`COUNT > 1` INT(10) default 0,
	PRIMARY KEY  (`START`,`STOP`)
  );
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO USR;
	 IF done THEN
      LEAVE read_loop;
    END IF;	
    SET TMP1=ST;  
    SET TMP2=DATE_ADD(TMP1, INTERVAL ITV MINUTE);      
    WHILE TMP1 < SP DO
	  SET NB=(select count(*) from location2_history where start>TMP1 and stop<TMP2 and domain=DOM and username=USR);
	  IF (NB>1) THEN
        INSERT INTO timetempo (`START`,`STOP`,`COUNT > 1`) VALUES (TMP1,TMP2,1) ON DUPLICATE KEY UPDATE `COUNT > 1`=`COUNT > 1`+1;
	  ELSE
        INSERT INTO timetempo (`START`,`STOP`,`COUNT = 1`) VALUES (TMP1,TMP2,1) ON DUPLICATE KEY UPDATE `COUNT = 1`=`COUNT = 1`+1;
	  END IF;  
      SET TMP1=TMP2;  
      SET TMP2=DATE_ADD(TMP1, INTERVAL ITV MINUTE);
    END WHILE;
  END LOOP;
  CLOSE cur;
  SELECT * FROM timetempo;
  DELETE FROM timetempo;
END|
delimiter ;

On 2011-06-17 11:40, Oussama Hammami wrote:

Correction:

mysql> CALL  StatReg2('91.121.142.9','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |       165 |        27 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |       166 |        26 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |       166 |        26 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |       165 |        27 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |       166 |        26 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |       166 |        26 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |       165 |        27 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |       166 |        26 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |       166 |        26 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |       166 |        26 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |       166 |        26 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |       166 |        26 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |       167 |        25 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |       167 |        25 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |       166 |        26 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |       166 |        26 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |       166 |        26 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |       165 |        27 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |       166 |        26 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |       166 |        26 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |       166 |        26 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |       165 |        27 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |       166 |        26 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |       165 |        27 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |       165 |        27 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |       165 |        27 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |       166 |        26 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |       166 |        26 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |       165 |        27 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |       166 |        26 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |       166 |        26 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |       166 |        26 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |       192 |         0 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |       192 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |       191 |         1 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |       191 |         1 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |       192 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |       192 |         0 |
| 2011-06-16 19:00:00 | 2011-06-16 19:30:00 |       192 |         0 |
| 2011-06-16 19:30:00 | 2011-06-16 20:00:00 |       192 |         0 |
| 2011-06-16 20:00:00 | 2011-06-16 20:30:00 |       191 |         1 |
| 2011-06-16 20:30:00 | 2011-06-16 21:00:00 |       192 |         0 |
| 2011-06-16 21:00:00 | 2011-06-16 21:30:00 |       192 |         0 |
| 2011-06-16 21:30:00 | 2011-06-16 22:00:00 |       192 |         0 |
| 2011-06-16 22:00:00 | 2011-06-16 22:30:00 |       192 |         0 |
| 2011-06-16 22:30:00 | 2011-06-16 23:00:00 |       192 |         0 |
| 2011-06-16 23:00:00 | 2011-06-16 23:30:00 |       192 |         0 |
| 2011-06-16 23:30:00 | 2011-06-17 00:00:00 |       192 |         0 |
| 2011-06-17 00:00:00 | 2011-06-17 00:30:00 |       192 |         0 |
| 2011-06-17 00:30:00 | 2011-06-17 01:00:00 |       192 |         0 |
| 2011-06-17 01:00:00 | 2011-06-17 01:30:00 |       191 |         1 |
| 2011-06-17 01:30:00 | 2011-06-17 02:00:00 |       192 |         0 |
| 2011-06-17 02:00:00 | 2011-06-17 02:30:00 |       192 |         0 |
| 2011-06-17 02:30:00 | 2011-06-17 03:00:00 |       192 |         0 |
| 2011-06-17 03:00:00 | 2011-06-17 03:30:00 |       192 |         0 |
| 2011-06-17 03:30:00 | 2011-06-17 04:00:00 |       192 |         0 |
| 2011-06-17 04:00:00 | 2011-06-17 04:30:00 |       192 |         0 |
| 2011-06-17 04:30:00 | 2011-06-17 05:00:00 |       192 |         0 |
| 2011-06-17 05:00:00 | 2011-06-17 05:30:00 |       192 |         0 |
| 2011-06-17 05:30:00 | 2011-06-17 06:00:00 |       192 |         0 |
| 2011-06-17 06:00:00 | 2011-06-17 06:30:00 |       192 |         0 |
| 2011-06-17 06:30:00 | 2011-06-17 07:00:00 |       192 |         0 |
| 2011-06-17 07:00:00 | 2011-06-17 07:30:00 |       192 |         0 |
| 2011-06-17 07:30:00 | 2011-06-17 08:00:00 |       191 |         1 |
| 2011-06-17 08:00:00 | 2011-06-17 08:30:00 |       191 |         1 |
| 2011-06-17 08:30:00 | 2011-06-17 09:00:00 |       191 |         1 |
| 2011-06-17 09:00:00 | 2011-06-17 09:30:00 |       192 |         0 |
| 2011-06-17 09:30:00 | 2011-06-17 10:00:00 |       192 |         0 |
| 2011-06-17 10:00:00 | 2011-06-17 10:30:00 |       192 |         0 |
| 2011-06-17 10:30:00 | 2011-06-17 11:00:00 |       192 |         0 |
| 2011-06-17 11:00:00 | 2011-06-17 11:30:00 |       192 |         0 |
| 2011-06-17 11:30:00 | 2011-06-17 12:00:00 |       192 |         0 |
+---------------------+---------------------+-----------+-----------+
72 rows in set (8.58 sec)

Query OK, 72 rows affected, 2 warnings (8.58 sec)

mysql> CALL  StatReg2('91.121.147.45','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |       121 |         6 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |       121 |         6 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |       121 |         6 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |       121 |         6 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |       121 |         6 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |       120 |         7 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |       121 |         6 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |       120 |         7 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |       121 |         6 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |       121 |         6 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |       121 |         6 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |       121 |         6 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |       121 |         6 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |       121 |         6 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |       120 |         7 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |       121 |         6 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |       120 |         7 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |       120 |         7 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |       120 |         7 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |       121 |         6 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |       121 |         6 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |       121 |         6 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |       122 |         5 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |       121 |         6 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |       121 |         6 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |       120 |         7 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |       121 |         6 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |       121 |         6 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |       121 |         6 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |       121 |         6 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |       121 |         6 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |       121 |         6 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |       125 |         2 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |       127 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |       127 |         0 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |       127 |         0 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |       127 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |       127 |         0 |
| 2011-06-16 19:00:00 | 2011-06-16 19:30:00 |       127 |         0 |
| 2011-06-16 19:30:00 | 2011-06-16 20:00:00 |       127 |         0 |
| 2011-06-16 20:00:00 | 2011-06-16 20:30:00 |       127 |         0 |
| 2011-06-16 20:30:00 | 2011-06-16 21:00:00 |       126 |         1 |
| 2011-06-16 21:00:00 | 2011-06-16 21:30:00 |       126 |         1 |
| 2011-06-16 21:30:00 | 2011-06-16 22:00:00 |       126 |         1 |
| 2011-06-16 22:00:00 | 2011-06-16 22:30:00 |       127 |         0 |
| 2011-06-16 22:30:00 | 2011-06-16 23:00:00 |       127 |         0 |
| 2011-06-16 23:00:00 | 2011-06-16 23:30:00 |       127 |         0 |
| 2011-06-16 23:30:00 | 2011-06-17 00:00:00 |       126 |         1 |
| 2011-06-17 00:00:00 | 2011-06-17 00:30:00 |       127 |         0 |
| 2011-06-17 00:30:00 | 2011-06-17 01:00:00 |       127 |         0 |
| 2011-06-17 01:00:00 | 2011-06-17 01:30:00 |       127 |         0 |
| 2011-06-17 01:30:00 | 2011-06-17 02:00:00 |       127 |         0 |
| 2011-06-17 02:00:00 | 2011-06-17 02:30:00 |       127 |         0 |
| 2011-06-17 02:30:00 | 2011-06-17 03:00:00 |       127 |         0 |
| 2011-06-17 03:00:00 | 2011-06-17 03:30:00 |       127 |         0 |
| 2011-06-17 03:30:00 | 2011-06-17 04:00:00 |       126 |         1 |
| 2011-06-17 04:00:00 | 2011-06-17 04:30:00 |       127 |         0 |
| 2011-06-17 04:30:00 | 2011-06-17 05:00:00 |       126 |         1 |
| 2011-06-17 05:00:00 | 2011-06-17 05:30:00 |       127 |         0 |
| 2011-06-17 05:30:00 | 2011-06-17 06:00:00 |       127 |         0 |
| 2011-06-17 06:00:00 | 2011-06-17 06:30:00 |       127 |         0 |
| 2011-06-17 06:30:00 | 2011-06-17 07:00:00 |       126 |         1 |
| 2011-06-17 07:00:00 | 2011-06-17 07:30:00 |       127 |         0 |
| 2011-06-17 07:30:00 | 2011-06-17 08:00:00 |       126 |         1 |
| 2011-06-17 08:00:00 | 2011-06-17 08:30:00 |       126 |         1 |
| 2011-06-17 08:30:00 | 2011-06-17 09:00:00 |       127 |         0 |
| 2011-06-17 09:00:00 | 2011-06-17 09:30:00 |       126 |         1 |
| 2011-06-17 09:30:00 | 2011-06-17 10:00:00 |       127 |         0 |
| 2011-06-17 10:00:00 | 2011-06-17 10:30:00 |       126 |         1 |
| 2011-06-17 10:30:00 | 2011-06-17 11:00:00 |       127 |         0 |
| 2011-06-17 11:00:00 | 2011-06-17 11:30:00 |       125 |         2 |
| 2011-06-17 11:30:00 | 2011-06-17 12:00:00 |       127 |         0 |
+---------------------+---------------------+-----------+-----------+
72 rows in set (4.90 sec)

Query OK, 72 rows affected, 2 warnings (4.90 sec)

On 2011-06-17 09:24, Task-By Emin Gabrielyan wrote:

Note that with this formula you must cover all telephones which are on-line. The numbers do not show all telephones. You mixed up something.

Emin Gabrielyan

Le Jun 17, 2011 à 1:59, Task-By Emin Gabrielyan <emin.gabrielyan@switzernet.com> a écrit :

There is something wrong about the numbers. The total of the two columns must be equal to the number of customers, if you respected the algorithm. Are you sure that the following condition is respected, and that you did not mix A with B?

Start < B and Stop > A (meaning any intersection)

Emin Gabrielyan

Le Jun 17, 2011 à 1:54, Task-By Emin Gabrielyan <emin.gabrielyan@switzernet.com> a écrit :

This is fantastically clear!

Bravo!

Emin

Emin Gabrielyan

Le Jun 16, 2011 à 19:22, Oussama Hammami <oussama.hammami@switzernet.com> a écrit :

Salut,

Ci-dessous les statistiques MySQL (DB3):

mysql> CALL  StatReg('91.121.142.9','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |         3 |        27 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |         2 |        26 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |         4 |        26 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |         1 |        27 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |         4 |        26 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |         1 |        26 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |         2 |        27 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |         1 |        26 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |         4 |        26 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |         1 |        26 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |         4 |        26 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |         2 |        26 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |         4 |        25 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |         3 |        25 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |         5 |        26 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |         1 |        26 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |         5 |        26 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |         1 |        27 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |         3 |        26 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |         2 |        26 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |         4 |        26 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |         0 |        27 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |         5 |        26 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |         2 |        27 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |         2 |        27 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |         2 |        27 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |         4 |        26 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |         1 |        26 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |         2 |        27 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |         1 |        26 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |         2 |        26 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |         2 |        26 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |         0 |         0 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |         2 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |         2 |         1 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |         1 |         1 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |         3 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |         5 |         0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.34 sec)

Query OK, 38 rows affected, 2 warnings (1.34 sec)

mysql> CALL  StatReg('91.121.147.45','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START               | STOP                | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 |         6 |         6 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 |         6 |         6 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 |         6 |         6 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 |         6 |         6 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 |         6 |         6 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 |         5 |         7 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 |         6 |         6 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 |         5 |         7 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 |         6 |         6 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 |         6 |         6 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 |         6 |         6 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 |         6 |         6 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 |         6 |         6 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 |         6 |         6 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 |         5 |         7 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 |         6 |         6 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 |         5 |         7 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 |         5 |         7 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 |         4 |         7 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 |         6 |         6 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 |         6 |         6 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 |         7 |         6 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 |         6 |         5 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 |         6 |         6 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 |         5 |         6 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 |         5 |         7 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |         5 |         6 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |         7 |         6 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |         6 |         6 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |         6 |         6 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |         6 |         6 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |         8 |         6 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |         8 |         2 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |         1 |         0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |         1 |         0 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |         2 |         0 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |         3 |         0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |         3 |         0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.28 sec)

Query OK, 38 rows affected, 2 warnings (1.28 sec)

On 2011-06-15 10:52, Task-By Emin Gabrielyan wrote:

Assume that we have the following location history table which is growing as a result of triggers of location table:
 Start
 Stop
 Domain
 Account
 IP
 Port
 Count

Assume we have a period from A to B.

For each account
  Count records
    Where
      Start < B and Stop > A (meaning any intersection)
  If count = 1
    Then the account does not loose its port
    Else the telephone looses the connection

A php script or an excel file must display hour by hour the percentage of accounts loosing their port.

Emin Gabrielyan

Le Jun 14, 2011 à 1:05, Emin Gabrielyan <emin.gabrielyan@switzernet.com> a écrit :

Need a clear quantity of telephones with lost ports on all servers. Write a script that can compute it automatically.

Envoyé de mon iPad