estimated.sql

DECLARE @orders TABLE (
  Id varchar(26)
)
INSERT INTO @orders
  VALUES ('Pro1'), ('PRO2'); --replace this with Pro#'s

SELECT
  ordr.PartnerPrimaryOrderID,
  ordr.PartnerReference2,
  (CASE
    WHEN del.EstimatedUnloadDate IS NOT NULL THEN del.EstimatedUnloadDate
    ELSE (CASE
        WHEN MovementText LIKE '%Estimated Arrival%' THEN SUBSTRING(MovementText, CHARINDEX(':', MovementText) + 1, LEN(MovementText))
        ELSE SUBSTRING(MovementText, CHARINDEX('>', MovementText) + 1, LEN(MovementText))
      END)
  END) AS ETA,
  (CASE
    WHEN del.ActualUnloadDate IS NOT NULL THEN del.ActualUnloadDate
    ELSE ordr.DLActualArrive
  END) AS ARRIVE,
  (CASE
    WHEN del.ActualUnloadDate IS NOT NULL THEN del.DLName
    ELSE ordr.DLName
  END) AS LOCATION,
  (CASE
    WHEN del.ActualUnloadDate IS NOT NULL THEN del.LoadID
    ELSE ordr.PartnerReference2
  END) AS LoadID
FROM [dbo].[tagnet_AppointmentSPN_Orders] ordr
LEFT JOIN (SELECT
  *
FROM tagnet_AppointmentSPN_OrderMovementEvents smv
WHERE ([MovementText] LIKE '%Estimated Arrival%'
OR MovementText LIKE '%Estimated Delivery%')
AND MovementDate = (SELECT
  MAX(MovementDate) AS MovementDate
FROM tagnet_AppointmentSPN_OrderMovementEvents
WHERE ([MovementText] LIKE '%Estimated Arrival%'
OR MovementText LIKE '%Estimated Delivery%')
AND JobRecID = smv.JobRecID)) mvmt
  ON ordr.JobRecID = mvmt.JobRecID
LEFT JOIN (SELECT
  *
FROM tagnet_AppointmentSPN_LoadTracking) ldt
  ON ldt.LoadID = ordr.PartnerReference2
LEFT JOIN (SELECT
  trk.*,
  ordr2.PartnerPrimaryOrderID,
  ordr3.DLName
FROM tagnet_AppointmentSPN_Orders ordr2
INNER JOIN tagnet_AppointmentSPN_LoadTracking trk
  ON trk.Destination = ordr2.ServiceProviderID
INNER JOIN tagnet_AppointmentSPN_Orders ordr3
  ON ordr2.PartnerPrimaryOrderID = ordr3.PartnerPrimaryOrderID
  AND ordr3.PartnerReference2 = trk.LoadID
WHERE ordr2.Jobtype = 'DELIVERY'
AND ordr2.orderState != 10) del
  ON del.PartnerPrimaryOrderID = ordr.PartnerPrimaryOrderID
WHERE ordr.PartnerPrimaryOrderID IN (SELECT
  Id
FROM @orders)
AND ordr.JobType = 'LINEHAUL'
AND ordr.OrderState != 10
AND ordr.CreatedDate = (SELECT
  MAX(CreatedDate) AS CreatedDate
FROM tagnet_AppointmentSPN_Orders tmp
WHERE tmp.PartnerPrimaryOrderID = ordr.PartnerPrimaryOrderID
AND tmp.JobType = 'LINEHAUL'
AND tmp.OrderState != 10
AND tmp.JobType = 'LINEHAUL')
GROUP BY ordr.PartnerPrimaryOrderID,
         ordr.PartnerReference2,
         ordr.DLActualArrive,
         del.ActualUnloadDate,
         del.EstimatedUnloadDate,
         del.LoadID,
         MovementText,
         del.DLName,
         ordr.DLName