April 28, 2014 at 6:24 am
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.
May 1, 2014 at 2:27 am
hisakimatama (4/8/2014)
Aha, so it doesn't :-). I've always declared CTEs with the column declarations, and by the wonderful problems of habit, it stuck as "the" way to do it. Well, I learned something that should make syntax a good bit clearer myself 😀
Declaring the columns in a CTE is a simple way of Aliasing or overriding the Alias in the inner select, the downside is that it also prevents you from simply adding columns without also adding them to the Output List, which is a PITA in a development environment.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply