satish,
the AS is technically optional, so you can remove it if you want. Other than that, my colleague and I can't see anything wrong with it. However, to make the query more efficient, I suppose the last line doesn't need to be:
AND Orders.Ship_Date <> dt.MaxShipDate.
It could be better written:
AND Orders.Ship_Date < dt.MaxShipDate
Hope you find it,
Jay
ps: what version of SQL are you running?