Luis Cazares (4/8/2014)
Since you're correcting this code, why don't we make it simpler and faster?
WITH UnloadDates AS(
SELECT ShipmentID,
MIN(starttime) StartTime,
MAX(Endtime) EndTime
FROM tblInvoice O
WHERE O.startTime IS NOT NULL
AND O.EndTime IS NOT NULL
AND O.DataSent is null
GROUP BY ShipmentID
)
SELECT ShipmentID,
StartTime,
EndTime
FROM UnloadDates
This way you read the table just once instead of trice. It's also a lot less code. 🙂
Max and Min, are DISTINCT measures.
I think that was missed by the poster.
Looks like they started piece at a time, and lost sight of how it all really fits together.
They may be reading out of a history file, which has change by change history.
Possible there may be a current record file, where the dates are populated, and the DataSent IS NULL could be used.
Or a physical file, where there is a logical view already built for this.
Something for them to investigate.