Can anyone tell me if there is a more prudent and efficient way of rewriting the following SQL?
asked Aug 30 '11 at 07:27 AM in Default
David 2 1
I'm not too sure about how the last enquiry agent column (if there is more than one hit for a client and date it will return more than one row), but the rest should be fine (Table scripts and test data would help out with testing):
Basically, CASE is your friend. You run through the table with one pass collecting all the different MAX() dates using case to decide if a row matches or not. You then join back to your client table. The last left join is to get the agent for the max(createdate) we found in the aggregate subquery.
This would all be much nicer if you had SQL 2005 or newer, as the window functions would do that nicely.