SQL - desglosar registros en SQL de forma variable

   
Vista:

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 claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

desglosar registros en SQL de forma variable

Publicado por leonardo_josue (878 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:

1
2
3
4
5
6
7
8
9
10
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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

desglosar registros en SQL de forma variable

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

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
                        60
                    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
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

desglosar registros en SQL de forma variable

Publicado por Isaias (690 intervenciones) el 06/03/2015 17:48:54
Primero, lo primero, ¿Qué base de datos manejas?, una muestra de tus datos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar