5 replies to this topic

[look]

Hola amigos , tengo la siguiente consulta:

php

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

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

 

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

 

 

esta es la consulta original sin agrupado:

php

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

resultado:

 

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!

[enecumene]

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

 

tienes esto:

sql

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

en vez de:

sql

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

Pero te aconsejo utilizar Sub- SELECT dentro de DATEDIFF

 

Saludos.

[look]

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

 

tienes esto:

sql

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

en vez de:

sql

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

[enecumene]

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

DATEDIFF(MIN, (select MIN(GPSTime) from VehiclesUpdates),(select MAX(GPSTime) from VehiclesUpdates))

O mejor algo así:

php

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.

[look]

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

DATEDIFF(MIN, (select MIN(GPSTime) from VehiclesUpdates),(select MAX(GPSTime) from VehiclesUpdates))

O mejor algo así:

php

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

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

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

[enecumene]

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

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.