[SOLUCIONADO] Consulta MYSQL a un campo texto que tiene fechas

[wesos]

Vengo del foro PHP que me han aconsejado que deje la duda en este foro. Bueno, quizás me daréis otra opción y estoy abierto a cualquiera.

Mi problema:
Tengo en una tabla mysql muchos artículos que debo obtener a través de una columna fecha (la cual es del tipo texto.. si.. texto y no me dejan modificarlo) y las fechas de tipo texto tienen este formato "14/10/2014", "20/11/2014", ect..

Entonces le paso a una función la cual realiza una consulta select, 4 parámetros (dia_inicial, mes_inicial, dia_final, mes_final) y con ese rango intento sacar todos los registro que estén dentro de esta fecha.

Sé que between podría pero al ser texto no me lo realiza bien la consulta.

Mi solución:(Que no funciona pero os podría dar una idea)

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM '.$tabla.' 
WHERE fechaalbaran LIKE "'.$dia1.'/'.$mes1.'/2014" 
GROUP BY descripcionarticulo ORDER BY count(descripcionarticulo) DESC
 

Editado: Código de programación no permitido en foros de Bases de Datos.
Leer las normas del foro, por favor.

Y la idea que me dijeron en el foro PHP es:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM '.$tabla.' WHERE CAST(fechaalbaran AS DATE) BETWEEN "01/10/2014" AND "04/10/2014"
 

La cual me gusta mucho y me ahorra código pero no me funciona..

¿Alguna propuesta, idea o arreglo?

[gnzsoloyo]

Vamos por el principio: Que tengas un campo de fecha pésimamente, ya es un problema grave. Y que no te lo dejen modificar, es peor.
¿Qué objeciones te ponen para cambiarlo?
Sería la solución mas simple, porque sería permanente. Ayudaría mucho a futuro, ya que no será la primera vez que te tomes con este problema...

Por otro lado, si no se puede modificar, en realidad la solución es usar funciones de conversión, siempre y cuando el contenido de ese campo sea siempre de una única fecha, y respete siempre el mismo formato.
Precisamente, el uso de columnas de tipo caracter para guardar fechas tienen el inconveniente que no permiten validar si lo que se almacena es o no una fecha correcta... Podría guardar cualquier basura.

Adicionalmente, te aconsejo que no uses LIKE para comparar datos que son magnitudes escalares. LIKE está definido para caracteres, y su uso sobre datos numéricos es totalmente desaconsejado.

Como sea, una solución aproximada sería esta:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM $tabla
WHERE fechaalbaran = STR_TO_DATE('".$dia1."'/'".$mes."'/2014', '%d/%m/%Y')
GROUP BY descripcionarticulo 
ORDER BY cantidad DESC
 

Dejo la parte de variables sólo para que te orientes. En este foro no se tratan temas de programación, como podrás leer en el link que puse en tu post.

La consulta final debe quedar mas o menos así, siendo, por ejemplo, el primero de diciembre de 2014:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM $tabla
WHERE fechaalbaran = STR_TO_DATE('01/12/2014', '%d/%m/%Y')
GROUP BY descripcionarticulo 
ORDER BY cantidad DESC
 

[wesos]

MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas). (La consulta tardó 0.3237 segundos.)

:(

[wesos]

Estoy pensando otra solución, cada registro tiene un número de albarán de valor numérico, ¿puedo realizar dos subconsultas para averiguar el primer número de albarán de cada fecha y así tener dos valores numéricos en los que hacer el betweeen?

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM RIVAS WHERE numeroalbaran BETWEEN 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = "27/09/2014") 
AND 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = "27/09/2014")
 

Me da error la sentencia, ¿pueden arreglármela en caso de poderse hacer?

[gnzsoloyo]

Iniciado por wesos

MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas). (La consulta tardó 0.3237 segundos.)

:(

Hay tres posibilidades para este error:
1) No existen datos que cumplan con el WHERE...
2) Que la fecha la tengas mal formateada en el registro.
3) Que las variables usadas no contengan lo que tu supones que tienen (eso es problema de programación)

Para el STR_TO_DATE() es lo mismo poner "1/12/2014", que poner "01/12/2014":

Código MySQL:

mysql> SELECT STR_TO_DATE('1/12/2014', '%d/%m/%Y') Fecha;
+------------+
| Fecha      |
+------------+
| 2014-12-01 |
+------------+
1 row in set (0.08 sec)
 
mysql>
mysql> SELECT STR_TO_DATE('01/12/2014', '%d/%m/%Y') Fecha;
+------------+
| Fecha      |
+------------+
| 2014-12-01 |
+------------+
1 row in set (0.00 sec)
 

Pero no es lo mismo si el texto tiene otro formato:

Código MySQL:

mysql> SELECT STR_TO_DATE(' 1.12.2014', '%d/%m/%Y') Fecha;
+-------+
| Fecha |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)
 
mysql> SELECT STR_TO_DATE('01-12-2014', '%d/%m/%Y') Fecha;
+-------+
| Fecha |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)
 

Verifica las variables, captura la sentencia tal y como fue creada, y finalmente postea una muestra real de los datos que tienes en la tabla, en esa columna.

[gnzsoloyo]

Iniciado por wesos

Estoy pensando otra solución, cada registro tiene un número de albarán de valor numérico, ¿puedo realizar dos subconsultas para averiguar el primer número de albarán de cada fecha y así tener dos valores numéricos en los que hacer el betweeen?

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM RIVAS WHERE numeroalbaran BETWEEN 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = "27/09/2014") 
AND 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = "27/09/2014")
 

Me da error la sentencia, ¿pueden arreglármela en caso de poderse hacer?

Poder, podrías, pero resulta ineficiente y el problema persiste.

Lo que sí te aconsejo no usar comillas para encerrar la fecha. Usa apostrofos (').
Para MySQL las dos cosas son diferentes, y no todas las instalaciones d MySQL toman las comillas como contenedores de texto. Depende de cierta configuración de arranque del Server.

Por otro lado, que digas "me da error" no es información útil...
¿Que error está devolviendo MYSQL? ¿Qué mensaje? ¿Qué numero?

[wesos]

Iniciado por gnzsoloyo

Poder, podrías, pero resulta ineficiente y el problema persiste.

Lo que sí te aconsejo no usar comillas para encerrar la fecha. Usa apostrofos (').
Para MySQL las dos cosas son diferentes, y no todas las instalaciones d MySQL toman las comillas como contenedores de texto. Depende de cierta configuración de arranque del Server.

Por otro lado, que digas "me da error" no es información útil...
¿Que error está devolviendo MYSQL? ¿Qué mensaje? ¿Qué numero?

Al ejecutar:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM RIVAS WHERE numeroalbaran BETWEEN 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = '27/09/2014') 
AND 
(SELECT numeroalbaran FROM `RIVAS` WHERE fechaalbaran = '27/09/2014')
 

Me sale:

Código:

Error:
#1242 - Subquery returns more than 1 row

[wesos]

Iniciado por gnzsoloyo

Hay tres posibilidades para este error:
1) No existen datos que cumplan con el WHERE...
2) Que la fecha la tengas mal formateada en el registro.
3) Que las variables usadas no contengan lo que tu supones que tienen (eso es problema de programación)

1) Si hay datos
2) La fecha es texto y está tal y como indica en la imagen inferior
3) Estoy realizando la consulta directamente en la BD, cuando me funcione la paso al código.

Al ejecutar:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad FROM RIVAS WHERE fechaalbaran = STR_TO_DATE('25/09/2014', '%d/%m/%Y') GROUP BY descripcionarticulo ORDER BY cantidad DESC
 

Me sale:

Código:

 MySQL ha devuelto un conjunto de valores vacío (es decir: cero columnas). (La consulta tardó 0.3806 segundos.)

Y mi tabla es:

[gnzsoloyo]

Error:
#1242 - Subquery returns more than 1 row

Bueno, es un error muy común en el aprendizaje... Las subconsultas están devolviendo múltiples registros, por lo que no puedes hacer una comparación por "=". Simplemente es eso.

Obviamente tienes en esa tabla dos o más registros con la misma fecha en ese campo, por lo que no puedes aplicar esa condición.

Volviendo al caso, ¿probaste esto?:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE DATE('2014-09-27') =  STR_TO_DATE(fechaalbaran, '%d/%m/%Y')
 

Como notarás, la anterior estaba mal escrita.

[wesos]

Muchas gracias por adelantando por el tiempo que te estás tomando.

[wesos]

Iniciado por gnzsoloyo

Bueno, es un error muy común en el aprendizaje... Las subconsultas están devolviendo múltiples registros, por lo que no puedes hacer una comparación por "=". Simplemente es eso.

Obviamente tienes en esa tabla dos o más registros con la misma fecha en ese campo, por lo que no puedes aplicar esa condición.

Volviendo al caso, ¿probaste esto?:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE DATE('2014-09-27') =  STR_TO_DATE(fechaalbaran, '%d/%m/%Y')
 

Como notarás, la anterior estaba mal escrita.

Ahora sí me devolvió un resultado, el primero de todos los que cumplen esa condición.

Código:

 Mostrando filas 0 - 0 (total de 1, La consulta tardó 0.3765 segundos.)

¿Y para hacer la consulta entre dos fechas?

[wesos]

Iniciado por gnzsoloyo

Bueno, es un error muy común en el aprendizaje... Las subconsultas están devolviendo múltiples registros, por lo que no puedes hacer una comparación por "=". Simplemente es eso.

Obviamente tienes en esa tabla dos o más registros con la misma fecha en ese campo, por lo que no puedes aplicar esa condición.

Volviendo al caso, ¿probaste esto?:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE DATE('2014-09-27') =  STR_TO_DATE(fechaalbaran, '%d/%m/%Y')
 

Como notarás, la anterior estaba mal escrita.

Utilizando tu consulta la he aplicado:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, count(descripcionarticulo) AS cantidad FROM RIVAS WHERE numeroalbaran BETWEEN 
(SELECT numeroalbaran, fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad FROM RIVAS WHERE DATE('2014-09-25') =  STR_TO_DATE(fechaalbaran, '%d/%m/%Y')) 
AND 
(SELECT numeroalbaran, fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad FROM RIVAS WHERE DATE('2014-09-27') =  STR_TO_DATE(fechaalbaran, '%d/%m/%Y'))
 

Y claro, me da este error:

Código:

#1241 - Operand should contain 1 column(s)

Tengo que hacer el select solo de numeroalbaran ¿no? pero como?..

[gnzsoloyo]

Por favor, prueba lo que te pasé, sin modificarla, y dime que te devuelve.
Repito NO la modifiques.

[wesos]

Iniciado por gnzsoloyo

Por favor, prueba lo que te pasé, sin modificarla, y dime que te devuelve.
Repito NO la modifiques.

Ya te lo dije más arriba, te dejo la imagen no obstante:

[gnzsoloyo]

OK. Eso es un poco más claro.
La consulta entre dos fechas es tan obvio como puede parecerlo... Usas un BETWEEN , pero no necesitas hacer subconsultas para eso, no tiene sentido, utilidad ni aprovechamiento.
No entiendo para qué las pones.

Código MySQL:

Esto si lo quieres discriminar por articulo...
SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE 
    STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
        BETWEEN  DATE('2014-09-25') AND  DATE('2014-09-27')
GROUP BY descripcionarticulo
 

Esto si quieres la sumatoria al final:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE 
    STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
        BETWEEN  DATE('2014-09-25') AND  DATE('2014-09-27')
GROUP BY descripcionarticulo WITH ROLLUP
 

[wesos]

Iniciado por gnzsoloyo

OK. Eso es un poco más claro.
La consulta entre dos fechas es tan obvio como puede parecerlo... Usas un BETWEEN , pero no necesitas hacer subconsultas para eso, no tiene sentido, utilidad ni aprovechamiento.
No entiendo para qué las pones.

Código MySQL:

Esto si lo quieres discriminar por articulo...
SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE 
    STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
        BETWEEN  DATE('2014-09-25') AND  DATE('2014-09-27')
GROUP BY descripcionarticulo
 

Esto si quieres la sumatoria al final:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE 
    STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
        BETWEEN  DATE('2014-09-25') AND  DATE('2014-09-27')
GROUP BY descripcionarticulo WITH ROLLUP
 

Si es bastante sencilla la consulta aunque sólo me da un resultado.. y hay muchísimos que están entre esas fechas:

[wesos]

Disculpa ahora sí, con la segunda consulta si me salen todos, muchas gracias!

[gnzsoloyo]

[wesos]

Iniciado por gnzsoloyo

Una cosita más :D, ¿y si quiero ordenar las cantidades de más a menos?

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
GROUP BY descripcionarticulo WITH ROLLUP 
ORDER BY COUNT(descripcionarticulo) desc
 

Error:

Código:

#1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY

[wesos]

Iniciado por wesos

Una cosita más :D, ¿y si quiero ordenar las cantidades de más a menos?

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad FROM RIVAS WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') GROUP BY descripcionarticulo WITH ROLLUP ORDER BY COUNT(descripcionarticulo) desc
 

Error:

Código:

#1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY

Me autocorrijo:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad FROM RIVAS WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') GROUP BY descripcionarticulo ORDER BY COUNT(descripcionarticulo) desc
 

[gnzsoloyo]

Prueba así:

Código SQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
GROUP BY descripcionarticulo WITH ROLLUP
ORDER BY cantidad;
 

o así:

Código SQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
GROUP BY descripcionarticulo DESC WITH ROLLUP;
 

DE todos modos de base es un error que uses un COUNT de esa forma en el ORDER BY

Ahora bien, ten en cuenta otro detalle: Al ordenar de esa forma, la linea de totalización será la primera en salir, y no la última.

[wesos]

Iniciado por gnzsoloyo

Prueba así:

Código SQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
GROUP BY descripcionarticulo WITH ROLLUP
ORDER BY cantidad;
 

o así:

Código SQL:

SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS 
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
GROUP BY descripcionarticulo DESC WITH ROLLUP;
 

DE todos modos de base es un error que uses un COUNT de esa forma en el ORDER BY

Ahora bien, ten en cuenta otro detalle: Al ordenar de esa forma, la linea de totalización será la primera en salir, y no la última.

Con la primera consulta me da este error:

Código:

#1221 - Incorrect usage of CUBE/ROLLUP and ORDER BY

Con la segunda consulta no me salen ordenados, no da error y el sumatorio me sale al final pero no estan ordenados.

Y es cierto, con mi consulta el sumatorio me aparece al principio y prefiero que esté al final.. ¿qué propones?

[gnzsoloyo]

Muestra como salen ordenados.
DEcir que no salen, no me resulta info útil... Necesito ver.

[wesos]

Iniciado por gnzsoloyo

Muestra como salen ordenados.
DEcir que no salen, no me resulta info útil... Necesito ver.

Vale jeje,

[gnzsoloyo]

Eso está bien ordenado...
Estás indicando que lo ordene por el campo de descripción de artículo, y eso es lo que hace.

Si quieres que lo ordene por las cantidades, debe hacerse de otro modo:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, cantidad 
FROM 
    (SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
    FROM RIVAS  
    WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
        BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
    GROUP BY descripcionarticulo) T1
ORDER BY cantidad DESC;
 

Y para que te devuelva la ultima linea, la cosa será algo complicada:

Código MySQL:

SELECT fechaalbaran, descripcionarticulo, cantidad 
FROM 
  (SELECT fechaalbaran, descripcionarticulo, cantidad 
  FROM 
      (SELECT fechaalbaran, descripcionarticulo, COUNT(descripcionarticulo) cantidad 
      FROM RIVAS  
      WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
          BETWEEN DATE('2014-12-01') AND DATE('2014-12-03') 
      GROUP BY descripcionarticulo) T1
  ORDER BY cantidad DESC)
UNION ALL
SELECT 'Total' fechaalbaran, '' descripcionarticulo, COUNT(descripcionarticulo) cantidad 
FROM RIVAS  
WHERE STR_TO_DATE(fechaalbaran, '%d/%m/%Y') 
  BETWEEN DATE('2014-12-01') AND DATE('2014-12-03');
 

[wesos]

No se cómo agradecerte esto.. de verdad muchas gracias, menudo curro!