question

David 2 1 avatar image
David 2 1 asked

Rewriting SQL Query To Eliminate Multiple Sub-Queries

Hi there, Can anyone tell me if there is a more prudent and efficient way of rewriting the following SQL? SELECT clientnum, title, forename, surname, --last enquiry date (SELECT TOP 1 createdate FROM log where log.clientnum = client.clientnum AND createdate IS NOT NULL AND reason = 'ENQUIRY' ORDER BY createdate DESC) AS last_inbound_enquiry_date, --last enquiry agent (SELECT TOP 1 agent FROM log where log.clientnum = client.clientnum AND createdate IS NOT NULL AND reason = 'ENQUIRY' ORDER BY createdate DESC) AS last_inbound_enquiry_staff, --last ongoing enquiry date (SELECT TOP 1 createdate FROM log where log.clientnum = client.clientnum AND createdate IS NOT NULL AND reason = 'ONGOING' AND outcome 'UNSUCCESS' ORDER BY createdate DESC) AS last_ongoing_enquiry_date, --last enquiry close date (SELECT TOP 1 createdate FROM log where log.clientnum = client.clientnum AND reason = 'ENQCLOSE' ORDER BY createdate DESC) AS last_enq_close_request_date, --last enquiry close date (SELECT TOP 1 createdate FROM log where log.clientnum = client.clientnum AND enquiry_close IS NOT NULL AND enquiry_close = 'Y' ORDER BY createdate DESC) AS last_enquiry_close_date, FROM client TIA
t-sqlsql-server-2000sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you my friend. So CASE passes through the table once but these sub queries would go pass through one time each? I'll test and let you know. Much appreciated.
0 Likes 0 ·
Your original sub-queries are correlated sub-queries. This means that they will be run for each row in the client table (the queries rely on the data supplied from the client table). My version will run once through the log table, collect all the info needed and then join back to the client table, this should be more efficient as it accesses the log table only once.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.