Comportamiento extraño en UPDATE..FROM usando SELECTs

[[rcarrillom]]

Un saludo al foro, tengo una función (SQL Server 2008 Express) que debe regresarme de una sola tabla Detalles dos resultados diferentes, a partir de un conjunto de datos que está en una tabla Maestro, uso 2 tablas:

• tblEvents - Catálogo de Eventos del Cargamento (Maestro)
• tblEventsLog - Bitácora de Eventos del Cargamento (Detalle)

Como no sé utilizar SQL mas que de manera básica, hago la operación en 3 pasos:

1. Filtrar y obtener del Detalle en general aquellos IDs de cargamentos que tienen al menos un evento sin hora capturada, obtengo el valor para un solo campo, es decir, cargamentos aún en entrega.
2. Del Detalle obtener el último evento con hora establecida de acuerdo a la secuencia filtrado por cada renglón del Maestro por ID de cargamento, obtengo valores para múltiples campos.
3. Del Detalle obtener el primer evento sin hora establecida de acuerdo a la secuencia filtrado por cada renglón del Maestro por ID de cargamento, obtengo valores para múltiples campos.

Cabe destacar que en el Detalle hay un conjunto de eventos secuenciales predefinidos que el usuario va llenando consecutivamente sin saltarse ninguno. Los problemas que tengo son:

• La consulta para el punto 2 regresa datos correctos solamente para el primer registro en Maestro (hora capturada) y la consulta para el punto 3 no regresa nada aún habiendo hora sin capturar para el registro actual en Maestro.
• La consulta para el punto 2 no me regresa datos para el último registro en Maestro (aún habiendo hora capturada) y la consulta para el punto 3 regresa datos correctos para el registro actual en Maestro.
• Si el registro siendo procesado de Maestro no es el primero ni el último, ninguna de las consultas para el punto 2 o 3 regresa datos aún habiendo datos correctos en Detalle, es como si los subquerys solo vieran el primer y último registro pero no los de enmedio de Maestro

La magia la debe de dar la sentencia (SELECT IDEvent FROM dbo.tblEvents WHERE IDCargo = [@Results].IDCargo) que es la que me da el filtro correcto, pero no me recupera los valores para cada IDCargo del registro de Detalle, se supone que debe reconocer la tabla @Results ya que es la única en el UPDATE, pero el SQL me obliga a ponerla en el FROM del subselect, y así según mi lógica no está recuperando el valor siendo procesado. Si no lo pongo me dice que no puede enlazar el identificador.

Con la linea Closed_Description = [@Results].IDCargo + ' / ' + SubQuery1.IDCargo, me doy cuenta que solamente actualiza el campo Closed_Description para el primer y último registro de Maestro, aún omitiendo la parte del WHERE del UPDATE.

Llevo días tratando de resolver esto y no doy con el problema, alguien le encuentra error de lógica? Me interesa que el (SELECT IDEvent FROM dbo.tblEvents WHERE IDCargo = [@Results].IDCargo) vea el valor correcto para [@Results].IDCargo sin incluirlo en el FROM.

He aquí la función problemática:

Código SQL [-]
ALTER FUNCTION [dbo].[fnGetOpenCargo]()
    RETURNS @Results TABLE
    (
        IDCargo      VARCHAR(50),

        Closed_IDEvent     UNIQUEIDENTIFIER,
        Closed_Sequence    INT,
        Closed_Event       INT,
        Closed_Description VARCHAR(200),
        Closed_IsStart     BIT,
        Closed_EventTime   DATETIME,
        
        Open_IDEvent     UNIQUEIDENTIFIER,
        Open_Sequence    INT,
        Open_Event       INT,
        Open_Description VARCHAR(200),
        Open_IsStart     BIT,
        Open_EventTime   DATETIME
    )
AS
BEGIN
  
  -- 1st stage, unfinished events ===============================================
  INSERT INTO @Results (IDCargo)
    -- Select only records with Unfinished events   
    SELECT
      IDCargo
    FROM
      (
        SELECT
          tblEvents.IDCargo, L.EventTime
        FROM
          dbo.tblEventsLog AS L
          RIGHT JOIN dbo.tblEvents ON dbo.tblEvents.IDEvent = L.IDEvent
      ) EventsLog
    GROUP BY
      IDCargo
    HAVING
      SUM(CASE WHEN (EventTime = 0 OR EventTime IS NULL) THEN 1 ELSE 0 END) > 0;
  
  -- 2nd stage, last closed event ===============================================
  UPDATE
    @Results
  SET
    Closed_IDEvent     = IDEvent,
    Closed_Sequence    = Sequence,
    Closed_Event       = Event,
    Closed_Description = [@Results].IDCargo + ' / ' + SubQuery1.IDCargo,
    Closed_IsStart     = IsStart,
    Closed_EventTime   = EventTime
  FROM  
    -- Select the last closed event
    (
      SELECT TOP 1
        tblEvents.IDCargo,
        L.IDEvent,
        L.Sequence,
        tblEvents.Event,
        tblEvents.Description,
        L.IsStart,
        L.EventTime
      FROM
        dbo.tblEventsLog AS L
        LEFT JOIN dbo.tblEvents ON dbo.tblEvents.IDEvent = L.IDEvent,
        @Results  -- <== Esto no debería estar aquí según mi precaria lógica, ya que el UPDATE lo incluye
      WHERE
        (L.EventTime <> 0 AND L.EventTime IS NOT NULL) AND
        L.IDEvent IN (SELECT IDEvent FROM dbo.tblEvents WHERE IDCargo = [@Results].IDCargo)
      ORDER BY
        L.Sequence DESC
     ) SubQuery1;
  --WHERE
  --  [@Results].IDCargo = SubQuery1.IDCargo;
  
  -- 3rd stage, first open event ================================================
  UPDATE
    @Results
  SET
    Open_IDEvent     = IDEvent,
    Open_Sequence    = Sequence,
    Open_Event       = Event,
    Open_Description = [@Results].IDCargo + ' / ' + SubQuery2.IDCargo,
    Open_IsStart     = IsStart,
    Open_EventTime   = EventTime
  FROM
    -- Select the first open event
    (    
      SELECT TOP 1
        tblEvents.IDCargo,
        L.IDEvent,
        L.Sequence,
        tblEvents.Event,
        tblEvents.Description,
        L.IsStart,
        L.EventTime
      FROM
        dbo.tblEventsLog AS L
        LEFT JOIN dbo.tblEvents ON dbo.tblEvents.IDEvent = L.IDEvent,
        @Results  -- <== Esto no debería estar aquí según mi precaria lógica, ya que el UPDATE lo incluye
      WHERE
        (L.EventTime = 0 OR L.EventTime IS NULL) AND
        L.IDEvent IN (SELECT IDEvent FROM dbo.tblEvents WHERE IDCargo = [@Results].IDCargo)
      ORDER BY
        L.Sequence
     ) SubQuery2;
  --WHERE
  --  [@Results].IDCargo = SubQuery2.IDCargo;

  RETURN
END

[[rcarrillom]]

Se me olvidaba, el resultado:

Código Delphi [-]
IDCargo  Closed_IDEvent  Closed_Sequence  Closed_Event  Closed_Description  Closed_IsStart  Closed_EventTime  Open_IDEvent  Open_Sequence  Open_Event  Open_Description  Open_IsStart  Open_EventTime
FLETE-000  FB575883-8705-4AA6-A267-EC8F1F750CAB  1  1  FLETE-000 / FLETE-000  1  2012-06-06 00:49:09.000  02E73029-5BFF-4F2E-947A-07F1DE9152EA  2  11  FLETE-000 / No borrar - Prueba  1  1900-01-01 00:00:00.000
FLETE-001  FB575883-8705-4AA6-A267-EC8F1F750CAB  1  1  FLETE-001 / FLETE-000  1  2012-06-06 00:49:09.000  02E73029-5BFF-4F2E-947A-07F1DE9152EA  2  11  FLETE-001 / No borrar - Prueba  1  1900-01-01 00:00:00.000
FLETE-003  FB575883-8705-4AA6-A267-EC8F1F750CAB  1  1  FLETE-003 / FLETE-000  1  2012-06-06 00:49:09.000  02E73029-5BFF-4F2E-947A-07F1DE9152EA  2  11  FLETE-003 / No borrar - Prueba  1  1900-01-01 00:00:00.000
flete-005  FB575883-8705-4AA6-A267-EC8F1F750CAB  1  1  flete-005 / FLETE-000  1  2012-06-06 00:49:09.000  02E73029-5BFF-4F2E-947A-07F1DE9152EA  2  11  flete-005 / No borrar - Prueba  1  1900-01-01 00:00:00.000
No borrar - Prueba  FB575883-8705-4AA6-A267-EC8F1F750CAB  1  1  No borrar - Prueba / FLETE-000  1  2012-06-06 00:49:09.000  02E73029-5BFF-4F2E-947A-07F1DE9152EA  2  11  No borrar - Prueba / No borrar - Prueba  1  1900-01-01 00:00:00.000

[[rcarrillom]]

Alguna ayuda?