sql-server-2008-r2 - desc - sql order by date

TSQL Order BY en ocasiones no ordena correctamente

Question

TSQL MSSQL 2008r2

Estoy volviendo a escribir la pregunta para intentar aclarar cuál es el problema que trato de explicar.

Tengo un proceso almacenado que toma 3 parámetros. VehicleKey, StartDate y EndDateTime. Estoy consultando un Data Warehouse db. Entonces la información no debería cambiar.

Cuando se llama al proceso con los mismos parámetros, la mayoría de las veces los resultados serán los esperados, pero en algunas ocasiones aleatorias, con esos mismos parámetros, los resultados difieren. Estoy consultando un WH de datos para que los datos no cambien.

El problema es con la columna derivada dinámica "Isla".

Es completamente aleatorio. El proceso se puede ejecutar 20 veces y dar los resultados esperados y los siguientes 2 darán resultados incorrectos.

Puede haber 1 o más combinaciones VehicleKey / DriverKey en un rango de fechas determinado.

Esta es la consulta de problema

SELECT
A.VehicleKey
,A.NodeId
,A.DriverKey
,MIN(A.StartTrip) ''StartTrip''
,MAX(A.EndTrip) ''EndTrip''
,SUM(A.PrivOdo) ''Private''
,SUM(A.BusOdo) ''Business''
,SUM(A.TravOdo) ''Travel''
,SUM(A.PrivOdo + A.BusOdo + A.TravOdo )''Total''
FROM
(
SELECT
Island = ( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey ORDER BY MONTH(StartTrip)) ) - ( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey, T.DriverKey ORDER BY T.StartTrip) )
,NodeId
,VehicleKey
,DriverKey
,StartTrip
,EndTrip
,BusOdo
,PrivOdo
,TravOdo
FROM
#xYTD_BPTotals T
) AS A
GROUP BY
A.Island
,A.VehicleKey
,A.NodeId
,A.DriverKey
ORDER BY
A.VehicleKey
,MIN(A.StartTrip); 

Entiendo que el ORDER BY debe estar en el exterior de la tabla derivada para que tenga efecto.

Creo que lo he reducido al problema que se presenta solo cuando un vehículo tiene 2 o más combinaciones de DriverKey.

por ejemplo, los Parameters VehicleKey 4865, StartDateTime = ''2016-01-01'', EndDateTime = ''2016-10-31'' Este es el resultado correcto, incluida la columna Island

VehicleKey NodeId DriverKey Island StartTrip EndTrip Private Business Travel Total_
4865 458 0 0 2016-09-06 14:06:08 2016-09-28 17:02:08 54.75 737.83 0 792.58
4865 458 1202 134 2016-09-29 11:10:04 2016-09-30 17:25:51 0 211.32 0 211.32
4865 458 0 27 2016-10-03 07:39:25 2016-10-14 17:00:15 0 579.81 0 579.81

y esto es cuando está mal. Parameters VehicleKey 4865, StartDateTime = ''2016-01-01'', EndDateTime = ''2016-10-31'' - incluida la columna Island Las dos primeras filas aquí deben combinarse.

VehicleKey NodeId DriverKey Island StartTrip EndTrip Private Business Travel Total_
4865 458 0 98 2016-09-06 14:06:08 2016-09-21 09:15:49 0 313.87 0 313.87
4865 458 0 -63 2016-09-21 09:21:10 2016-09-28 17:02:08 54.75 423.96 0 478.71
4865 458 1202 71 2016-09-29 11:10:04 2016-09-30 17:25:51 0 211.32 0 211.32
4865 458 0 27 2016-10-03 07:39:25 2016-10-14 17:00:15 0 579.81 0 579.81

Si muestro las primeras filas de la tabla derivada, he desglosado la columna "Isla"

SELECT
Island = ( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey ORDER BY MONTH(StartTrip)) ) - ( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey, T.DriverKey ORDER BY T.StartTrip) )
,Island_x =( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey ORDER BY MONTH(StartTrip)) )
,Island_y = ( ROW_NUMBER() OVER (PARTITION BY T.VehicleKey, T.DriverKey ORDER BY T.StartTrip) )
,NodeId
,VehicleKey
,DriverKey
,StartTrip
,EndTrip
,BusOdo
,PrivOdo
,TravOdo
FROM
#xYTD_BPTotals T

El resultado correcto debe ser

Island Island_x Island_y NodeId VehicleKey DriverKey StartTrip EndTrip BusOdo PrivOdo TravOdo
0 1 1 24901 4865 0 2016-09-06 14:06:08 2016-09-06 14:08:50 0 0 0
0 2 2 24901 4865 0 2016-09-06 15:39:14 2016-09-06 15:40:53 114 0 0
0 3 3 24901 4865 0 2016-09-08 11:06:43 2016-09-08 11:07:23 0 0 0
0 4 4 24901 4865 0 2016-09-08 11:12:03 2016-09-08 11:12:26 20 0 0
0 5 5 24901 4865 0 2016-09-08 11:19:20 2016-09-08 11:19:52 1 0 0
0 6 6 24901 4865 0 2016-09-08 11:26:58 2016-09-08 11:27:56 88 0 0
0 7 7 24901 4865 0 2016-09-08 11:33:40 2016-09-08 11:35:02 1 0 0
0 8 8 24901 4865 0 2016-09-12 09:08:53 2016-09-12 09:10:42 34 0 0

pero a veces obtengo esto con los mismos parámetros de entrada.

Island Island_x Island_y NodeId VehicleKey DriverKey StartTrip EndTrip BusOdo PrivOdo TravOdo
98 1 1 24901 4865 0 2016-09-06 14:06:08 2016-09-06 14:08:50 0 0 0
98 2 2 24901 4865 0 2016-09-06 15:39:14 2016-09-06 15:40:53 114 0 0
98 3 3 24901 4865 0 2016-09-08 11:06:43 2016-09-08 11:07:23 0 0 0
98 4 4 24901 4865 0 2016-09-08 11:12:03 2016-09-08 11:12:26 20 0 0
98 5 5 24901 4865 0 2016-09-08 11:19:20 2016-09-08 11:19:52 1 0 0
98 6 6 24901 4865 0 2016-09-08 11:26:58 2016-09-08 11:27:56 88 0 0
98 7 7 24901 4865 0 2016-09-08 11:33:40 2016-09-08 11:35:02 1 0 0
98 8 8 24901 4865 0 2016-09-12 09:08:53 2016-09-12 09:10:42 34 0 0

¿Por qué la columna calculada "Isla" es incorrecta? 1-1 = 0 no 98.

¿Dónde estoy equivocado?

Answer 1

EDITAR - @YourData ahora se parece a su tabla cruda

Declare @YourTable table (VehicleKey int,NodeId int,DriverKey int,StartTrip datetime,EndTrip datetime,PrivOdo decimal(10,2),BusOdo decimal(10,2), TravOdo decimal(10,2))
Insert Into @YourTable values
(4865,458,0 ,''2016-09-06 14:06:08'',''2016-09-21 09:15:49'',0 ,313.87,0),
(4865,458,0 ,''2016-09-21 09:21:10'',''2016-09-28 17:02:08'',54.75,423.96,0),
(4865,458,1202,''2016-09-29 11:10:04'',''2016-09-30 17:25:51'',0 ,211.32,0),
(4865,458,0 ,''2016-10-03 07:39:25'',''2016-10-14 17:00:15'',0 ,579.81,0)
Select VehicleKey
,NodeID
,VehicleKey
,DriverKey
,StartTrip = min(StartTrip)
,EndTrip = max(EndTrip)
,Private = sum(PrivOdo)
,Business = sum(BusOdo)
,Travel = sum(TravOdo)
,Total = sum(PrivOdo + BusOdo + TravOdo )
From (
Select Island = ( ROW_NUMBER() OVER (PARTITION BY VehicleKey ORDER BY MONTH(StartTrip)) ) - ( ROW_NUMBER() OVER (PARTITION BY VehicleKey, DriverKey ORDER BY StartTrip) )
,*
From @YourTable
) A
Group By Island,VehicleKey,NodeID,VehicleKey,DriverKey
Order By min(StartTrip)

Devoluciones

FYI - La sub-consulta produce