SQL - desglosar registros en SQL de forma variable

desglosar registros en SQL de forma variable

Publicado por osasa (4 intervenciones) el 06/03/2015 14:02:51

Hola a todos. Tengo una duda en la que ojalá alguien me pueda orientar:
Tengo una tabla donde tengo los siguientes campos:

id
inicio
fin

Como ejemplo pondré que tengo un registro en la base de datos donde un trabajador comenzó su recorrido a las 10:45 y lo acabó a las 12:30.
Actualmente tengo con una sencilla select el tiempo que ha tardado en realizar el recorrido con un datediff del inicio y el fin del mismo, pero necesito poder calcular en cada hora que esté dentro del tiempo del recorrido, la cantidad de minutos que se han utilizado de forma que si un recorrido empezó a las 10:45 y acabó a las 12:30, la base de datos me devuelva tantos registros como horas haya entre la hora de inicio y fin (en este caso 3 registros):

id // inicio // fin // hora // minutos
1 // 10:45 // 12:30 // 10 // 15
1 // 10:45 // 12:30 // 11 // 60
1 // 10:45 // 12:30 // 12 // 30

¿a alguien se le ocurre como podría solucionarlo?
Muchas gracias por adelantado.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 806

desglosar registros en SQL de forma variable

Publicado por leonardo_josue (1173 intervenciones) el 06/03/2015 17:40:13

Hola osasa:

La consulta que planteas no es para nada Trivial... lo más sencillo es que hicieras esto de manera programática, es decir, si estás trabajando con algún lenguaje de programación (Java, PHP, .NET, etc), es más simple que implementes con este lenguaje una posible solución. El problema con SQL es que sólo trabaja con datos existentes, es decir, no puede "inventar" información.

Sin embargo, se me ocurrió una posible solución que igual y te puede ayudar. Para esto, necesitas una tabla que indica el inicio y fin de cada hora, es decir, algo como esto:

mysql> SELECT * FROM horas;

+------+------------+----------+

| id   | horaInicio | horaFin  |

+------+------------+----------+

|   10 | 10:00:00   | 10:59:59 |

|   11 | 11:00:00   | 11:59:59 |

|   12 | 12:00:00   | 12:59:59 |

|   13 | 13:00:00   | 13:59:59 |

+------+------------+----------+

4 rows in set (0.02 sec)

Ahora bien, según lo que nos comentas, tu tabla de visitas o recorridos, tiene más o menos esta estructura:

mysql> SELECT * FROM visitas;

+------+----------+----------+

| id   | inicio   | fin      |

+------+----------+----------+

|    1 | 10:45:00 | 12:30:00 |

|    2 | 11:10:00 | 11:30:00 |

|    3 | 10:00:00 | 11:00:00 |

+------+----------+----------+

3 rows in set (0.00 sec)

Ahora, lo que puedes hacer es un JOIN con condiciones especiales, donde cheques los rangos de las horas, se me ocurre que hagas algo como esto:

mysql> SELECT *

    -> FROM visitas

    -> INNER JOIN horas ON

    ->    visitas.inicio BETWEEN horas.horaInicio AND horas.horaFin  OR

    ->    visitas.fin BETWEEN horas.horaInicio AND horas.horaFin OR

    ->    horas.horaInicio BETWEEN visitas.inicio AND visitas.fin

    -> ORDER BY visitas.id, horas.id;

+------+----------+----------+------+------------+----------+

| id   | inicio   | fin      | id   | horaInicio | horaFin  |

+------+----------+----------+------+------------+----------+

|    1 | 10:45:00 | 12:30:00 |   10 | 10:00:00   | 10:59:59 |

|    1 | 10:45:00 | 12:30:00 |   11 | 11:00:00   | 11:59:59 |

|    1 | 10:45:00 | 12:30:00 |   12 | 12:00:00   | 12:59:59 |

|    2 | 11:10:00 | 11:30:00 |   11 | 11:00:00   | 11:59:59 |

|    3 | 10:00:00 | 11:00:00 |   10 | 10:00:00   | 10:59:59 |

|    3 | 10:00:00 | 11:00:00 |   11 | 11:00:00   | 11:59:59 |

+------+----------+----------+------+------------+----------+

6 rows in set (0.00 sec)

Esto se acerca mucho a lo que necesitas... te faltaría determinar cuántos minutos utiliza para cada hora, es decir, la columna MINUTOS que tienes en tu consulta... para el cálculo te serviría la función DATEDIFF, pero tienes que "jugar" con los campos y poner algunas condiciones para obtener lo que necesitas. Haz la prueba y nos comentas.

Saludos
Leo

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

desglosar registros en SQL de forma variable

Publicado por osasa (4 intervenciones) el 08/03/2015 12:53:33

Muchisimas gracias Leo, tu idea me vino "al pelo".
Os dejo la Select que hice para solucionarlo creando una tabla que contuviera todas las horas del día. En el id_percurso 5, 6 y 8 se ve como lo desgloso.

SELECT id_percurso, inicio, fin,hora,

fecha + inicio as ini_percurso,

case when fin > inicio then fecha + fin else fecha + fin + 1 end as fin_percurso,

case

    when (hora = hour(inicio) and hora = hour(fin)) then

    minute(fin) - minute(inicio)

    else

            case

                when  hora = hour(inicio) then

                60 - minute(inicio)

                else

                    case

                        when hora = hour(fin)  then

                        minute(fin)

                    else

end

end

end

as minutos

FROM percursos

INNER JOIN horas ON

    percursos.inicio BETWEEN horas.horaInicio AND horas.horaFin  OR

    percursos.fin BETWEEN horas.horaInicio AND horas.horaFin OR

    horas.horaInicio BETWEEN ini_percurso AND fin_percurso order by id_percurso, hora

Gracias a todos.

Valora esta respuesta

Comentar

Pos: 1

Val: 2.542

desglosar registros en SQL de forma variable

Publicado por Isaias (1921 intervenciones) el 06/03/2015 17:48:54

Primero, lo primero, ¿Qué base de datos manejas?, una muestra de tus datos

Valora esta respuesta

Comentar

orientarme Select

Entender mejor las clausulas de SELECT