Rewriting SQL Query To Eliminate Multiple Sub-Queries

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):

SELECT  c.clientnum, 
       c.title,
       c.forename, 
       c.surname,
       last_inbound_enquiry_date,
       l2.agent as last_inbound_enquiry_staff,
       last_ongoing_enquiry_date,
       last_enq_close_request_date,
       last_enquiry_close_date
FROM client c
LEFT JOIN (
SELECT  clientnum,
       MAX(CASE WHEN reason ='ENQUIRY' THEN createdate ELSE NULL END) AS last_inbound_enquiry_date,
       MAX(CASE WHEN reason ='ONGOING' AND outcome <>'UNSUCCESS' THEN createdate ELSE NULL END) AS  last_ongoing_enquiry_date,
       MAX(CASE WHEN reason ='ENQCLOSE' THEN createdate ELSE NULL END) AS last_enq_close_request_date,
       MAX(CASE WHEN enquiry_close = 'Y' THEN createdate ELSE NULL END) AS last_enquiry_close_date
FROM Log) l ON l.clientnum = c.clientnum
LEFT JOIN Log l2 ON l2.clientnum = l.clientnum AND l2.createdate=last_inbound_enquiry_date

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.