SELECT
wo.Number AS WorkOrderNumber
,uh.SerialNumberInternal
,rs.Number AS RouteStep
,op.Name AS Operation
,dbo.LocalDateTime(1, uhStart.DateCreated, 1) AS DateTimeStart
,dbo.LocalDateTime(1, uh.DateCreated, 1) AS DateTimeComplete
,DATEDIFF(MILLISECOND, uhStart.DateCreated, uh.DateCreated) AS DurationInMillisecond
,TransactionPurpose.CodingValueEnglish AS Task
,TransactionResult.CodingValueEnglish AS Result
,ua.LegalName
,ua.EmployeeNumber
,uh.StationCreatedAt
FROM UnitHistory AS uh WITH(NOLOCK)
LEFT JOIN RouteStep AS rs WITH(NOLOCK)
ON uh.[RouteStepId] = rs.[RouteStepId]
LEFT JOIN Operation AS op WITH(NOLOCK)
ON uh.[OperationId] = op.[OperationId]
LEFT JOIN WorkOrder AS wo WITH(NOLOCK)
ON uh.[WorkOrderId] = wo.[WorkOrderId]
LEFT JOIN dbo.[utf_LocalizedReferenceBySelector]('en-US', 'TransactionPurpose') AS TransactionPurpose
ON uh.[ReferenceIdTransactionPurpose] = TransactionPurpose.ReferenceId
LEFT JOIN dbo.[utf_LocalizedReferenceBySelector]('en-US', 'TransactionResult') AS TransactionResult
ON uh.[ReferenceIdTransactionResult] = TransactionResult.ReferenceId
LEFT JOIN UserAccount AS ua WITH(NOLOCK)
ON uh.UserAccountIdCreated = ua.UserAccountId
CROSS APPLY (
SELECT
uhs.DateCreated
,uhs.HourReferenceKey
,uhs.OperationId
FROM
UnitHistory AS uhs WITH(NOLOCK)
WHERE uhs.WorkOrderId = 172561
AND uhs.ReferenceIdTransactionPurpose = 132
AND uh.HourReferenceKey = uhS.HourReferenceKey
AND uh.OperationId = uhS.OperationId
AND uhs.UnitId = uh.UnitId
) AS uhStart
WHERE uh.WorkOrderId = 172561
AND uh.ReferenceIdTransactionPurpose = 133
ORDER BY uh.SerialNumberInternal, rs.Number , uh.DateCreated