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:
- 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.
- 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.
- 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
