Ir al contenido



Foto

Obtener minutos entre dos fechas con agrupamiento

sql group by datediff

  • Por favor identifícate para responder
5 respuestas en este tema

#1 look

look

    Advanced Member

  • Miembros
  • PipPipPip
  • 377 mensajes
  • LocationLa Ceiba-Atlantida-Honduras

Escrito 25 febrero 2016 - 11:08

Hola amigos , tengo la siguiente consulta:


php
  1. SELECT
  2. min(upd.Streets) as Streets,
  3. min(upd.GPSTime) start_time,
  4. max(upd.GPSTime) end_time,
  5. datediff(mi, min(upd.GPSTime) , max(upd.GPSTime))as mins,
  6. min( str.State) as state
  7. FROM Vehicles veh
  8. join VehicleUpdates upd on veh.VehId=upd.VehId
  9. join StreetsLookup str on upd.EntryID=str.EntryID
  10. WHERE (veh.VehId=85) AND upd.Speed <> -1 AND upd.Latitude <> 999 AND upd.Longitude <> 999 AND upd.GPSTime
  11. BETWEEN CONVERT(DateTime, '2016-02-24' , 120) AND CONVERT(DateTime, '2016-02-25' , 120)
  12. GROUP BY upd.Streets
  13. order by start_time

esta me devuelve los minutos acumilados entre start_time  y end_time agrupado por el campo streets.

4kQuWrL.png

 

El problema que tengo es que no me calcula bien los minutos en algunos agrupados:

 

g9eB5vj.png

 

esta es la consulta original sin agrupado:


php
  1. SELECT
  2. upd.Streets,
  3. (upd.GPSTime) start_time,
  4. (upd.GPSTime) end_time,
  5. str.State
  6. FROM Vehicles veh
  7. join VehicleUpdates upd on veh.VehId=upd.VehId
  8. join StreetsLookup str on upd.EntryID=str.EntryID
  9. WHERE (veh.VehId=85) AND upd.Speed <> -1 AND upd.Latitude <> 999 AND upd.Longitude <> 999 AND upd.GPSTime
  10. BETWEEN CONVERT(DateTime, '2016-02-24' , 120) AND CONVERT(DateTime, '2016-02-25' , 120)
  11. order by start_time

resultado:

mI7fJa2.png

 

el agrupado lo hace bien en algunos casos , pero no se porque no me obtiene bien los minutos en algunos registros agrupados.

 

aqui un excel con los datos de la consulta original de donde se agrupan los datos:

 

https://docs.google....dit?usp=sharing

 

 

 

lo que quiero hacer es obtener el primer y ultimo dato por grupo de la consulta, de este modo podre sacar la diferencia entre los dos datos

 

espero me puedan dar una ayudita ...

 

Saludos!


  • 0

#2 enecumene

enecumene

    Webmaster

  • Administrador
  • 7.115 mensajes
  • LocationRepública Dominicana

Escrito 25 febrero 2016 - 01:54

Hay un pequeño error en la función DATEDIFF, haces referencia a los minutos como mi en vez de MIN:

 

tienes esto:


sql
  1. datediff(mi, MIN(upd.GPSTime) , MAX(upd.GPSTime))AS mins

en vez de:


sql
  1. datediff(MIN, MIN(upd.GPSTime) , MAX(upd.GPSTime))AS mins

Pero te aconsejo utilizar Sub- SELECT dentro de DATEDIFF

 

Saludos.


  • 0

#3 look

look

    Advanced Member

  • Miembros
  • PipPipPip
  • 377 mensajes
  • LocationLa Ceiba-Atlantida-Honduras

Escrito 25 febrero 2016 - 02:12

Hay un pequeño error en la función DATEDIFF, haces referencia a los minutos como mi en vez de MIN:

 

tienes esto:


sql
  1. datediff(mi, MIN(upd.GPSTime) , MAX(upd.GPSTime))AS mins

en vez de:


sql
  1. datediff(MIN, MIN(upd.GPSTime) , MAX(upd.GPSTime))AS mins

Pero te aconsejo utilizar Sub- SELECT dentro de DATEDIFF

 

Saludos.

 

hola amigo, lo del "mi" , no tengo ningun problema de sintaxis, por otro lado de confundira con la fincion min "minimo" no crees?, en fin...

 

realmente lo que necesito es obtener el primer valor y ultimo por agrupado,  lo extraño es que a nivel de reporte logro obtener el resultado que deseo , pero no puedo lograr hacerlo con una sql


  • 0

#4 enecumene

enecumene

    Webmaster

  • Administrador
  • 7.115 mensajes
  • LocationRepública Dominicana

Escrito 25 febrero 2016 - 02:36

No, no se confundirá, la sintaxis correcta es con MIN, por eso te sugerí que utilizaras subs-selects, porque DATEDIFF no asume MIN(upd.GPSTime) como minutos sino, como el tiempo menor, algo como:


php
  1. DATEDIFF(MIN, (select MIN(GPSTime) from VehiclesUpdates),(select MAX(GPSTime) from VehiclesUpdates))

O mejor algo así:


php
  1. DATEDIFF(MIN, (select GPSTime from VehiclesUpdates order by GPSTime ASC LIMIT 1),(select GPSTime from VehiclesUpdates order by GPSTime DESC LIMIT 1))

Ahí tienes dos opciones, sólo pruebalas.

 

Saludos.


  • 0

#5 look

look

    Advanced Member

  • Miembros
  • PipPipPip
  • 377 mensajes
  • LocationLa Ceiba-Atlantida-Honduras

Escrito 25 febrero 2016 - 03:12

No, no se confundirá, la sintaxis correcta es con MIN, por eso te sugerí que utilizaras subs-selects, porque DATEDIFF no asume MIN(upd.GPSTime) como minutos sino, como el tiempo menor, algo como:


php
  1. DATEDIFF(MIN, (select MIN(GPSTime) from VehiclesUpdates),(select MAX(GPSTime) from VehiclesUpdates))

O mejor algo así:


php
  1. DATEDIFF(MIN, (select GPSTime from VehiclesUpdates order by GPSTime ASC LIMIT 1),(select GPSTime from VehiclesUpdates order by GPSTime DESC LIMIT 1))

Ahí tienes dos opciones, sólo pruebalas.

 

Saludos.

 

Hola amigo, no funciona bien,

 

hasta ahora modifique la consulta y a quedado asi,


php
  1. select distinct upd.streets
  2. ,first_value(upd.GPSTime) over (partition by upd.streets order by upd.GPSTime) as [start]
  3. ,first_value(upd.GPSTime) over (partition by upd.streets order by upd.GPSTime desc) as [end]
  4. ,datediff(mi
  5. ,first_value(upd.GPSTime) over (partition by upd.streets order by upd.GPSTime)
  6. ,first_value(upd.GPSTime) over (partition by upd.streets order by upd.GPSTime desc)
  7. )
  8. FROM Vehicles veh
  9. join VehicleUpdates upd on veh.VehId=upd.VehId
  10. join StreetsLookup str on upd.EntryID=str.EntryID
  11. WHERE (veh.VehId=85) AND upd.Speed <> -1 AND upd.Latitude <> 999 AND upd.Longitude <> 999 AND upd.GPSTime
  12. BETWEEN CONVERT(DateTime, '2016-02-24' , 120) AND CONVERT(DateTime, '2016-02-25' , 120)
  13. order by start

y esta me devuelve el mismo resultado , no devuelve correctamente el ultimo valor el del "last_value" o "min()" :/  8o|


  • 0

#6 enecumene

enecumene

    Webmaster

  • Administrador
  • 7.115 mensajes
  • LocationRepública Dominicana

Escrito 26 febrero 2016 - 02:45

He estado haciendo pruebas de diferentes formas y todas dan igual, lo último que hice fue convertirlo en TIME para obviar la fecha para ver si afectaba en algo, luego obtener el resultado en segundos y asi hacerle un CAST divido entre 60 y el resultado sigue siendo el mismo que las imagenes que presentas:


sql
  1. SELECT CAST(DATEDIFF(SECOND,CONVERT(TIME,(SELECT [gpstime] FROM [pruebas].[dbo].[tracking] ORDER BY [gpstime] DESC offset 1 ROWS)),CONVERT(TIME,(SELECT [gpstime] FROM [pruebas].[dbo].[tracking] ORDER BY [gpstime] ASC offset 1 ROWS)))/60 AS VARCHAR(10))

Saludos.
  • 0





Etiquetado también con una o más de estas palabras: sql, group by, datediff