x

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
more ▼

asked Aug 30, 2011 at 07:27 AM in Default

David 2 1 gravatar image

David 2 1
412 46 50 52

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Aug 30, 2011 at 07:46 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Aug 30, 2011 at 08:04 AM David 2 1
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.
Aug 30, 2011 at 11:57 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x476
x46

asked: Aug 30, 2011 at 07:27 AM

Seen: 1867 times

Last Updated: Aug 30, 2011 at 07:27 AM