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