Tema: Error en función MySql
Ver Mensaje Individual
  #13 (permalink)  
Antiguo 13/07/2016, 13:31
solilopi
 
Fecha de Ingreso: enero-2012
Mensajes: 52
Antigüedad: 12 años, 2 meses
Puntos: 1
Pregunta Respuesta: Error en función MySql
Hola, ya lo he implementado en la función y sigue devolviendo 0. La función queda:
Código MySQL: 
Ver original
  1. CREATE DEFINER=`root`@`localhost` FUNCTION `dias_sin`() RETURNS smallint(6)
  2. BEGIN
  3.  
  4. declare contador integer;
  5. declare dif date;
  6. declare resultado smallint default 0;
  7. declare ultimo integer default 0;
  8.  
  9. SELECT MAX(salida_num)INTO ultimo FROM datos;
  10.  
  11. set contador = 14;
  12.  
  13. repeat       
  14.      
  15.      SELECT MAX(DATEDIFF(dt1.fecha, dt2.fecha)) into dif FROM datos dt1 
  16.      INNER JOIN datos dt2 ON dt1.salida_num = dt2.salida_num + 1;
  17.      
  18.     if resultado < cast(dif as unsigned) then
  19.     
  20.         set resultado = cast(dif as unsigned);
  21.         
  22.     end if;
  23.     
  24.     set contador = contador+1;
  25.         
  26.     until contador = ultimo
  27.     
  28.     end repeat;
  29.     
  30. RETURN resultado;
  31.  
  32. END
La tabla datos es:
Código MySQL: 
Ver original
  1. CREATE TABLE `datos` (
  2.   `salida_num` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3.   `recorrido` varchar(50) COLLATE utf8_spanish2_ci NOT NULL,
  4.   `tiempo` varchar(8) COLLATE utf8_spanish2_ci NOT NULL,
  5.   `distancia` decimal(5,2) NOT NULL,
  6.   `calorias` decimal(6,2) NOT NULL,
  7.   `kmh` decimal(4,2) NOT NULL,
  8.   `fecha` date NOT NULL,
  9.   PRIMARY KEY (`salida_num`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;
el insert es:
Código MySQL: 
Ver original
  1. INSERT INTO `datos` VALUES (1,'Casa-Blayet','01:47:22',36.37,855.30,20.32,'2013-01-01'),(2,'Casa-El Perelló','02:10:03',44.76,1036.00,20.65,'2013-01-01'),(3,'Casa-El Perelló','02:08:15',44.76,1021.67,20.94,'2013-01-01'),(4,'Casa-El Perelló','02:04:07',44.76,988.74,21.64,'2013-01-01'),(5,'Casa-El Recatí','01:50:53',39.81,883.32,21.54,'2013-01-01'),(6,'Casa-Barraca','02:15:48',49.78,1081.81,21.99,'2013-01-01'),(7,'Casa-Barraca','02:10:38',49.78,1250.74,22.86,'2013-01-01'),(8,'Casa-Blayet','01:33:23',36.37,894.09,23.37,'2013-01-01'),(9,'Casa-Blayet','01:32:25',36.37,884.83,23.61,'2013-01-01'),(10,'Casa-Blayet','01:37:09',36.37,773.92,22.46,'2013-01-01'),(11,'Casa-Barraca','02:06:12',49.78,1208.29,23.67,'2013-01-01'),(12,'Casa-Blayet','01:42:28',36.51,845.08,21.38,'2013-01-01'),(13,'Casa-El Perelló','01:54:49',45.44,1138.10,23.75,'2013-01-01'),(14,'Casa-Blayet','01:30:59',36.51,988.00,24.10,'2013-05-24'),(15,'Casa-Blayet','01:33:10',36.93,923.00,23.80,'2013-05-25'),(16,'Casa-Barraca','02:02:25',49.48,1329.00,24.30,'2013-06-15'),(17,'Casa-Blayet','01:29:08',35.89,1212.00,24.30,'2013-06-17'),(18,'Casa-Blayet','01:31:17',36.70,991.00,24.20,'2013-06-20'),(19,'Casa-Barraca','02:03:16',49.59,1338.00,24.20,'2013-06-29'),(20,'Casa-Barraca','01:59:56',49.57,1302.00,24.80,'2013-07-05'),(21,'Casa-Blayet','01:26:11',36.03,936.00,25.22,'2013-07-08'),(22,'Casa-Blayet','01:27:05',36.98,945.00,25.50,'2013-07-17'),(23,'Casa-Barraca','01:56:28',49.19,1236.00,25.50,'2013-07-22'),(24,'Casa-Blayet','01:28:29',36.17,939.00,24.70,'2013-07-23'),(25,'Casa-Blayet','01:33:20',36.39,904.00,23.50,'2013-07-25'),(26,'Casa-Barraca','01:56:44',49.25,1239.00,25.40,'2013-07-29'),(27,'Casa-Blayet','01:28:16',36.33,936.00,24.80,'2013-07-30'),(28,'Casa-Blayet','01:43:15',36.76,832.00,21.40,'2013-08-01'),(29,'Casa-Blayet','01:26:59',36.64,923.00,25.40,'2013-08-02'),(30,'Casa-Blayet','01:25:26',36.03,906.00,25.40,'2013-08-05'),(31,'Casa-Blayet','01:24:07',36.05,978.00,25.71,'2013-08-24'),(32,'Casa-Blayet','01:26:41',36.00,920.00,25.00,'2013-08-31'),(33,'Casa-Blayet','01:38:52',36.56,796.00,22.20,'2013-09-13'),(34,'Casa-Blayet','01:26:09',35.93,914.00,25.02,'2013-09-21'),(35,'Casa-Blayet','01:32:27',36.59,915.00,23.40,'2013-10-05'),(36,'Casa-Barraca','01:59:38',49.65,1269.00,25.00,'2013-10-19'),(37,'Casa-Barraca','02:01:06',49.32,1285.00,24.50,'2013-10-21'),(38,'Casa-Blayet','01:26:43',35.93,920.00,25.00,'2013-10-28'),(39,'Casa-Blayet','01:34:05',36.48,911.00,23.40,'2013-12-06'),(40,'Casa-Barraca(Castellar)','02:16:24',51.44,1275.00,22.63,'2014-01-11'),(41,'Casa-Blayet','01:34:08',36.13,880.00,23.10,'2014-03-17'),(42,'Casa-Blayet','01:34:12',36.18,880.00,23.20,'2014-03-18'),(43,'Casa-Barraca','01:56:12',49.28,1190.00,25.50,'2014-04-18'),(44,'Casa-Barraca','01:56:46',49.51,1196.00,25.50,'2014-04-21'),(45,'Casa-Barraca','02:01:02',49.73,1239.00,24.80,'2014-04-28'),(46,'Casa-Barraca','01:59:21',49.78,1222.00,25.03,'2014-04-30'),(47,'Casa-Barraca','02:03:59',49.74,1270.00,24.20,'2014-05-03'),(48,'Casa-Barraca','02:00:58',49.85,1239.00,23.50,'2014-05-10'),(49,'Casa-Blayet','01:28:50',36.08,909.00,24.50,'2014-05-23'),(50,'Casa-Sueca-Silla','02:48:14',61.88,1308.00,21.80,'2014-05-24'),(51,'Casa-Venta Cabrera-Picassent','02:01:18',41.65,943.00,20.70,'2014-06-01'),(52,'Casa-Barraca','02:02:23',49.58,1253.00,24.31,'2014-06-28'),(53,'Casa-Cullera','02:30:02',63.23,1536.00,25.40,'2014-07-05'),(54,'Casa-Blayet','01:27:59',36.01,901.00,24.70,'2014-07-09'),(55,'Casa-Barraca','01:55:18',49.11,1294.00,25.70,'2014-07-14'),(56,'Casa-Barraca','02:00:30',49.65,1234.00,24.80,'2014-07-16'),(57,'Casa-Barraca','01:59:18',49.13,1222.00,24.80,'2014-07-23'),(58,'Casa-Cullera','02:23:15',62.97,1608.00,26.50,'2014-07-26'),(59,'Casa-Cullera','02:38:37',62.85,1482.00,23.80,'2014-08-07'),(60,'Casa-Barraca','02:00:47',49.11,1237.28,24.40,'2014-08-08'),(61,'Casa-Barraca','01:55:36',49.15,1298.18,25.51,'2014-08-11'),(62,'Casa-Barraca','01:56:37',48.87,1194.61,25.14,'2014-09-13'),(63,'Casa-Barraca','01:57:11',49.11,1200.00,24.50,'2014-08-08'),(64,'Casa-Barraca','01:53:58',49.50,1279.00,25.50,'2014-08-11'),(65,'Casa-Cullera','02:30:34',62.75,1542.00,25.00,'2014-09-13'),(66,'Casa-Puzzle','01:55:03',45.74,1075.00,22.90,'2014-09-17'),(67,'Casa-Cullera','02:28:02',62.88,1516.00,25.50,'2014-09-20'),(68,'Casa-Barraca','01:58:50',49.10,1217.00,24.80,'2014-09-25'),(69,'Casa-Barraca','02:00:48',49.48,1237.00,24.20,'2014-10-02'),(70,'Casa-Barraca','02:02:23',49.02,1283.00,24.00,'2014-10-04'),(71,'Casa-Barraca','02:02:46',49.16,1287.00,24.00,'2014-10-09'),(72,'Casa-Cullera','02:34:50',62.13,1624.00,24.20,'2014-10-11'),(73,'Casa-Cullera-Sueca (El Romani)','02:50:25',70.35,1787.00,24.80,'2014-10-18'),(74,'Casa-Barraca','01:56:53',49.14,1785.00,24.80,'2014-10-26'),(75,'Casa-Cullera','02:30:33',62.75,1579.00,25.00,'2014-11-01'),(76,'Casa-Cullera','02:53:34',63.49,2410.00,21.30,'2014-11-15'),(77,'Casa-Cullera','02:38:56',62.92,1521.00,23.80,'2014-11-22'),(78,'Casa-Barraca','02:07:23',49.58,1219.62,23.35,'2014-12-06'),(79,'Casa-Barraca','02:02:32',49.14,1285.46,24.06,'2014-12-22'),(80,'Casa-Blayet','01:35:54',36.08,918.18,22.60,'2014-12-23'),(81,'Casa-Barraca','02:03:31',49.43,1295.78,24.01,'2015-02-28'),(82,'Casa-Barraca','02:08:36',49.13,1231.27,22.92,'2015-03-07'),(83,'Casa-Barraca','02:10:22',49.21,1248.19,22.65,'2015-04-03'),(84,'Casa-Cullera','02:47:08',63.32,1600.20,22.73,'2015-04-09'),(85,'Casa-Cullera','02:43:45',62.94,1567.81,23.06,'2015-04-11'),(86,'Casa-Cullera','02:37:32',63.38,1652.63,24.14,'2015-04-18'),(87,'Casa-Cullera','02:43:09',63.23,1562.06,23.25,'2015-04-25'),(88,'Casa-Barraca','02:14:42',49.16,1073.05,21.90,'2015-05-01'),(89,'Casa-Cullera','02:37:38',62.87,1509.24,23.93,'2015-05-09'),(90,'Casa-Cullera','02:41:14',62.29,1543.71,23.18,'2015-05-16'),(91,'Casa-Cullera','02:32:36',62.80,1639.00,24.80,'2015-05-23'),(92,'Casa-Cullera','02:34:48',62.49,1623.96,24.22,'2015-05-30'),(93,'Casa-Barraca','02:08:35',49.24,1231.11,22.98,'2015-06-02'),(94,'Casa-Barraca','02:07:24',49.17,1219.78,23.16,'2015-06-04'),(95,'Casa-Cullera','02:41:42',62.88,1493.54,23.33,'2015-06-06'),(96,'Casa-Barraca','02:05:22',49.23,1157.95,23.56,'2015-06-09'),(97,'Casa-Cullera','02:34:57',63.48,1568.16,24.58,'2015-06-20'),(98,'Casa-Barraca','02:12:13',49.37,1016.09,22.40,'2015-06-23'),(99,'Casa-Barraca','02:00:59',49.38,1224.40,24.49,'2015-06-30'),(100,'Casa-Cullera','02:37:46',62.79,1457.21,23.88,'2015-07-04'),(101,'Casa-Cullera','02:25:09',62.77,1610.38,25.95,'2015-07-12'),(102,'Casa-Barraca','01:59:54',49.07,1213.44,24.56,'2015-07-16'),(103,'Casa-Cullera','02:27:42',62.76,1476.56,25.49,'2015-07-25'),(104,'Casa-Cullera','02:31:43',62.75,1479.27,24.82,'2015-08-02'),(105,'Casa-Cullera','02:30:17',62.73,1465.29,25.04,'2015-08-10'),(106,'Casa-Barraca','02:06:09',49.16,1122.55,23.38,'2015-08-11'),(107,'Casa-Barraca','02:11:19',49.39,1168.53,22.57,'2015-08-13'),(108,'Casa-Cullera','02:30:00',62.73,1462.53,25.09,'2015-08-17'),(109,'Casa-Cullera','02:32:11',62.59,1483.81,24.68,'2015-08-22'),(110,'Casa-Cullera','02:33:06',62.74,1492.75,24.59,'2015-08-29'),(111,'Casa-Cullera','02:33:06',62.67,1511.65,24.56,'2015-09-05'),(112,'Casa-Cullera-Sueca','03:02:32',73.14,1802.26,24.04,'2015-09-12'),(113,'Casa-Cullera','02:29:42',62.74,1478.08,25.15,'2015-09-20'),(114,'Casa-Mareny Blau','02:21:34',56.10,1275.69,23.78,'2015-10-03'),(115,'Casa-Cullera','02:36:59',62.78,1414.61,23.99,'2015-10-17'),(116,'Casa-Cullera','02:37:26',62.76,1418.66,23.92,'2015-11-09'),(117,'Casa-Cullera','02:36:00',62.78,1540.28,24.15,'2015-11-14'),(118,'Casa-Barraca','02:11:35',48.93,986.56,22.31,'2015-11-28'),(119,'Casa-Barraca','02:05:24',49.19,1158.25,23.54,'2016-01-23'),(120,'Casa-Barraca','02:05:17',49.81,1157.17,23.85,'2016-01-30'),(121,'Casa-Barraca','02:02:56',49.14,1135.47,23.98,'2016-02-06'),(122,'Casa-Cullera','02:37:38',63.50,1595.32,24.17,'2016-03-26'),(123,'Casa-Barraca','02:23:21',49.90,1101.65,20.89,'2016-04-02'),(124,'Casa-Barraca','02:03:26',49.43,1249.20,24.03,'2016-05-01'),(125,'Casa-Cullera(Castellar)','02:30:40',65.55,1671.59,26.10,'2016-05-07'),(126,'Casa-Barraca','02:08:34',49.37,1187.51,23.04,'2016-05-21'),(127,'Casa-Barraca','02:10:47',49.36,1207.97,22.65,'2016-06-04'),(128,'Casa-Barraca','02:07:34',49.55,1778.00,23.31,'2016-06-07'),(129,'Casa-Barraca','02:11:40',49.86,1834.00,22.72,'2016-06-09'),(130,'Casa-Barraca','02:13:40',49.82,1862.00,22.36,'2016-06-11'),(131,'Casa-Cullera','02:34:03',63.47,2228.00,24.72,'2016-06-25'),(132,'Casa-Cullera','02:35:40',63.44,2132.00,24.44,'2016-07-02');
Creo que con esto ya puedes tener mejor idea de lo que tengo, ya te digo, al ejecutar la función devuelve 0
Última edición por gnzsoloyo; 13/07/2016 a las 15:30